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.
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: NVL and 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)