COALESCE

The COALESCE function returns the first non-null expression in the expression list. If all occurrences of expression evaluate to NULL, then the function returns NULL.

SQL syntax

COALESCE(Expression1, Expression2 [,...])

Parameters

COALESCE has the parameters:

Parameter Description

Expression1, Expression2 [,...]

The expressions in the expression list. The first non-null expression in the expression list is returned.

Each expression is evaluated in order and there must be at least two expressions.

Description

  • This function is a generalization of the NVL function.

  • Use COALESCE as a variation of the CASE Expressions. For example: 

    COALESCE (Expression1, Expression2)

    is equivalent to:

    CASE WHEN Expression1 IS NOT NULL THEN Expression1
   ELSE Expression2
END

Examples

The example illustrates the use of the COALESCE expression. The COALESCE expression is used to return the commission_pct for the first 10 employees with manager_id = 100. If the commission_pct is NOT NULL, then the original value for commission_pct is returned. If commission_pct is NULL, then 0 is returned. 

Command> SELECT FIRST 10 employee_id, COALESCE (commission_pct, 0) FROM
          employees WHERE manager_id = 100;
< 101, 0 >
< 102, 0 >
< 114, 0 >
< 120, 0 >
< 121, 0 >
< 122, 0 >
< 123, 0 >
< 124, 0 >
< 145, .4 >
< 146, .3 >
10 rows found.