Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

COALESCE Function

Description

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.

  • CHAR < VARCHAR
  • NCHAR < NCHAR VARING
  • BIT < VARBIT
  • SHORT < INT < BIGINT < NUMERIC < FLOAT < DOUBLE
  • DATE < TIMESTAMP < DATETIME

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.

Syntax

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

Example

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