COALESCE

​

Syntax

​

Scalar

coalesce(value1 [, value2 ...])

• All result alternatives must have compatible types. Matching types are preserved; mixed types may be converted to a common type. Use explicit casts to choose the result type.

​

Arguments

​

Returns

• Type: Common SQL type after conversion of its arguments
• NULL behavior: Returns NULL when every argument is NULL.

​

Properties

​

Usage notes

• A single argument returns that argument. At least one argument is required.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

SELECT coalesce(CAST(NULL AS INT), 5, 8) AS "value", coalesce(CAST(NULL AS INT), CAST(NULL AS INT)) AS all_null;

​

Choose the first available string

SELECT coalesce(CAST(NULL AS VARCHAR), 'unknown', 'unused') AS chosen;

​

Related functions

• nvl