AntDB-Oracle Compatibility Developer’s Manual P3–66

COALESCE

The COALESCE function returns the first non-null value in the argument. The null value is returned only if all the arguments are null.

COALESCE(value [, value2 ] ...)

When it is necessary to display the extracted value, we usually use the default value instead of the null value.

select COALESCE(value1 , value2, default_value)…

As with CASE expressions, COALESCE does not compute parameters that are not needed to determine the result. This means that the arguments to the right of the first non-null argument are not computed. This SQL standard function provides similar functionality to the functions NVL and IFNULL, which can be used in other database systems.

Example:

create table test(id int, math int,chinese int, english int);
insert into test(id, math) values(1,88);
insert into test(id, english) values(2,98);
insert into test(id, chinese) values(3,97);
insert into test(id) values(4);

SELECT COALESCE(math, chinese, english, 0) from test;
COALESCE 
-------------------
       88
       98
       97
        0
(4 rows)

SELECT COALESCE(math, chinese, english) from test; 
COALESCE 
------------------
       88
       98
       97

(4 rows)