COALESCE function The COALESCE function takes two or more compatible arguments and returns the first argument that is not null. COALESCE function functionsCOALESCE

The result is null only if all the arguments are null.

If all the parameters of the function call are dynamic, an error occurs.

A synonym for COALESCE is VALUE. VALUE is accepted by but is not recognized by the SQL standard.
Syntax COALESCE ( expression, expression [ , expression ]* )

The function must have at least two arguments.

 Example ij> -- create table with three different integer types ij> create table temp(smallintcol smallint, bigintcol bigint, intcol integer); 0 rows inserted/updated/deleted ij> insert into temp values (1, null, null); 1 row inserted/updated/deleted ij> insert into temp values (null, 2, null); 1 row inserted/updated/deleted ij> insert into temp values (null, null, 3); 1 row inserted/updated/deleted ij> select * from temp; SMALL&|BIGINTCOL |INTCOL --------------------------------------- 1 |NULL |NULL NULL |2 |NULL NULL |NULL |3 3 rows selected ij> -- the return data type of coalesce is bigint ij> select coalesce (smallintcol, bigintcol) from temp; 1 -------------------- 1 2 NULL 3 rows selected ij> -- the return data type of coalesce is bigint ij> select coalesce (smallintcol, bigintcol, intcol) from temp; 1 -------------------- 1 2 3 3 rows selected ij> -- the return data type of coalesce is integer ij> select coalesce (smallintcol, intcol) from temp; 1 ----------- 1 NULL 3 3 rows selected