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.
- 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