COALESCE Function

The COALESCE function returns the first non-NULL value from a series of expressions.

COALESCE Function

1  COALESCE ( expression? +  , expression )
Element Description Restrictions Syntax
expression An expression that returns a value of a compatible data type with the other expressions in the function Cannot be a host variable or a BYTE or TEXT object Expression

The expressions are evaluated in the order in which they are specified, and the result of the function is the first value that is not null. The result of the COALESCE function returns NULL only if all the arguments are null. The expressions can return any data type that can be cast to a common compatible data type.

Suppose that the addr column of the employees table has NULL values in some rows, and that you want to print the label Address unknown for these rows. Run the following SELECT statement to display the label Address unknown when the addr column has a NULL value: 
SELECT fname, COALESCE (addr, 'Address unknown') AS address 
   FROM employees;