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

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

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

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

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)