Categories:: Conditional Expression Functions

COALESCE¶

Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.

Syntax¶

COALESCE( <expr1> , <expr2> [ , ... , <exprN> ] )

Usage Notes¶

All arguments must have the same data type.

Collation Details¶

The collation specifications of all input arguments must be compatible.
The comparisons follow the collation based on the input arguments’ collations and precedences.
The collation of the result of the function is the highest-precedence collation of the inputs.

Examples¶

SELECT column1, column2, column3, coalesce(column1, column2, column3)
FROM (values
  (1,    2,    3   ),
  (null, 2,    3   ),
  (null, null, 3   ),
  (null, null, null),
  (1,    null, 3   ),
  (1,    null, null),
  (1,    2,    null)
) v;

+---------+---------+---------+-------------------------------------+
| COLUMN1 | COLUMN2 | COLUMN3 | COALESCE(COLUMN1, COLUMN2, COLUMN3) |
|---------+---------+---------+-------------------------------------|
|       1 |       2 |       3 |                                   1 |
|    NULL |       2 |       3 |                                   2 |
|    NULL |    NULL |       3 |                                   3 |
|    NULL |    NULL |    NULL |                                NULL |
|       1 |    NULL |       3 |                                   1 |
|       1 |    NULL |    NULL |                                   1 |
|       1 |       2 |    NULL |                                   1 |
+---------+---------+---------+-------------------------------------+