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 |
+---------+---------+---------+-------------------------------------+