COALESCE

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

The arguments can be of either a built-in or user-defined data type.

The COALESCE function cannot be used as a source function when creating a user-defined function.

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 selected argument is converted, if necessary, to the attributes of the result. If the COALESCE function has more than two arguments, the rules are applied to the first two arguments to determine a candidate result type. The rules are then applied to that candidate result type and the third argument to determine another candidate result type. This process continues until all arguments are analyzed and the final result type is determined.

If there are any mixed character string or graphic string and numeric arguments, the string value is implicitly cast to a DECFLOAT(34) value.

The COALESCE function can also handle a subset of the functions provided by CASE expressions. The result of using COALESCE(e1,e2) is the same as using the expression:

CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END

VALUE can be specified as a synonym for COALESCE.

SELECT COALESCE(DATEOFJOIN, CURRENT_DATE) AS JOINDATE FROM EMPLOYEE;

JOINDATE
2019-11-25
2020-01-06
2019-10-10
2019-11-27
2019-11-25
2019-11-25
2020-01-06