COALESCE

The COALESCE function returns the value of the first non-null expression.

The arguments must be compatible. Character-string arguments are compatible with datetime values. For more information about data type compatibility, see Assignments and comparisons.

expression-1: An expression that returns a value of any built-in or user-defined data type. ^¹
expression-2: An expression that returns a value of any built-in or user-defined data type. ^¹

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. The result can be null only if all arguments can be null, and the result is null only if all arguments are null.

The selected argument is converted, if necessary, to the attributes of the result. The attributes of the result are determined by all the operands as explained in Rules for result data types.

Note

Syntax alternatives: Start of change NVL and End of change VALUE are synonyms for COALESCE.

Examples

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
```
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
```
When working with several global variables that contain dates, return a non-null date value. When DATE1 is null and DATE2 is not null, DATE2 will be returned. If DATE2 is also null, the value of the CURRENT DATE special register will be returned.
```
VALUES COALESCE(DATE1, DATE2, CURRENT DATE)
   
```

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