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:

Description

• This function is a generalization of the NVL function.

• Use COALESCE as a variation of the CASE expression. 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.