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)
Top comments (0)