COALESCE

Specifies a series of expressions and returns the first expression whose value is not NULL. If all the expressions evaluate as null, COALESCE returns a NULL value.

Syntax

COALESCE(expression1, expression2[...])

The COALESCE syntax is shorthand notation for a common case that can also be represented in a CASE expression. The following two formulations are equivalent:

COALESCE ( expression1 , expression2 , expression3 )

CASE
WHEN expression1 IS NOT NULL THEN expression1
WHEN expression2 IS NOT NULL THEN expression2
ELSE expression3
END

Notes

This function is not allowed in a GROUP BY clause.

Arguments to this function cannot be query expressions.

Example

This example illustrates the COALESCE function:

SELECT COALESCE (end_date, start_date) from job_hist;

Compatibility

SQL compatible