Skip to main content

COALESCE function

Returns the first non-NULL argument from a list of expressions.

Syntax

COALESCE(expression1, expression2 [, expression3, ...])

Arguments

  • expression1, expression2, etc.: Two or more expressions of compatible types.

Returns

The first non-NULL value from the argument list. The return type is the common type of all arguments.

Limits

  • At least two arguments must be provided.

  • All arguments must be of compatible types.

  • At least one argument must have a concrete (non-NULL) type. COALESCE(NULL) or COALESCE(NULL, NULL, ...) where all arguments are NULL literals is not allowed because NULL is not a valid output type in CeloSQL. Alternatively, cast the result to a supported output type.

Examples

-- Example 1: Return first non-NULL value
> SELECT COALESCE(NULL, 'hello', 'world');
'hello'

-- Example 2: First argument is not NULL
> SELECT COALESCE('first', 'second', 'third');
'first'

-- Example 3: Using COALESCE with all NULL arguments but casting to a supported output type
> SELECT CAST(COALESCE(NULL) AS BIGINT);
NULL

-- Example 4: Error when all arguments are NULL literals
> SELECT COALESCE(NULL);
ERROR: NULL is not a supported output type

Notes

  • COALESCE is equivalent to a CASE expression:

    COALESCE(a, b, c)
-- is equivalent to:
CASE WHEN a IS NOT NULL THEN a
     WHEN b IS NOT NULL THEN b
     ELSE c
END

See Also