The COALESCE function has more than one expression as an argument. If a first argument is non-NULL, the corresponding value is returned if it is NULL, a second argument is returned. If all expressions which have an argument are NULL, NULL is returned. Therefore, this function is generally used to replace NULL with other default value.
Operation is performed by converting the type of every argument into that with the highest priority. If there is an argument whose type cannot be converted, the type of every argument is converted into a VARCHAR type. The following list shows priority of conversion based on input argument type.
For example, if a type of a is INT, b, BIGINT, c, SHORT, and d, FLOAT, then COALESCE(a, b, c, d) returns a FLOAT type. If a type of a is INTEGER, b, DOULBE, c, FLOAT, and d, TIMESTAMP, then COALESCE(a, b, c, d) returns a VARCHAR type.
COALESCE(expression [, ...])
result :
expression | NULL
COALESCE(a, b) works the same as the CASE statement as follows:
CASE WHEN a IS NOT NULL
THEN a
ELSE b
END
SELECT * FROM case_tbl;
a
=============
1
2
3
NULL
--substituting a default value 10.0000 for NULL valuse
SELECT a, COALESCE(a, 10.0000) FROM case_tbl;
a coalesce(a, 10.0000)
===================================
1 1.0000
2 2.0000
3 3.0000
NULL 10.0000