COALESCE scalar function

The COALESCE function returns the first non-null expression in a list of expressions.

Read syntax diagramSkip visual syntax diagramCOALESCE(expression1 ,expression2 )

The schema is SYSIBM.

expression1
An expression that returns a value of any built-in or user-defined data type.
expression2
An expression that returns a value of any built-in or user-defined data type and that is compatible with the data type of expression1. Which data types are compatible with each other is described in Rules for result data types.

The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. If all the arguments are null, the result is null.

Notes

  • The COALESCE function cannot be used as a source function when creating a user-defined function. Because this function accepts any compatible data types as arguments, it is not necessary to create additional signatures to support user-defined data types.

Examples

  • Example 1: When selecting all the values from all the rows in the DEPARTMENT table, if the department manager (MGRNO) is missing (that is, null), then return a value of 'ABSENT'.
       SELECT DEPTNO, DEPTNAME, COALESCE(MGRNO, 'ABSENT'), ADMRDEPT
         FROM DEPARTMENT
  • Example 2: When selecting the employee number (EMPNO) and salary (SALARY) from all the rows in the EMPLOYEE table, if the salary is missing (that is, null), then return a value of zero.
       SELECT EMPNO, COALESCE(SALARY, 0)
         FROM EMPLOYEE
  • Example 3: In the following COALESCE statement, if the value of c1 is:
    • 5, the statement returns a value of 5
    • NULL, the statement returns a value of 10
    • 'AB', the statement returns an error, because the data types of the two expressions are incompatible
    COALESCE(c1,10)