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