COALESCE

Returns the first expr[1,... N] that is not NULL. If all expr[1,... N] are NULL, NULL is returned.

Syntax

COALESCE(expr1, expr2[, expr3, ... exprN])

Usage Notes

All expr[1...N] arguments must be of the same data type.

Examples

select col1, col2, col3, coalesce(col1, col2, col3)
    from (             select 1    as col1, 2    as col2, 3    as col3  
            union all  select NULL as col1, 2    as col2, 3    as col3   
            union all  select NULL as col1, NULL as col2, 3    as col3   
            union all  select NULL as col1, NULL as col2, NULL as col3
            union all  select 1    as col1, NULL as col2, 3    as col3   
            union all  select 1    as col1, NULL as col2, NULL as col3
            union all  select 1    as col1, 2    as col2, NULL as col3);

+---------+---------+---------+-------------------------------------+
|   COL1  |   COL2  |   COL3  |      COALESCE(COL1, COL2, COL3)     |
|---------+---------+---------+-------------------------------------|
|       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 |
+---------+---------+---------+-------------------------------------+

select col1, col2, col3, coalesce(col1, col2, col3)
    from(              select 'a' as col1, 'b'  as col2, 'c'  as col3  
            union all  select 'a' as col1, 'b'  as col2, NULL as col3   
            union all  select 'a' as col1, NULL as col2, NULL as col3);

+---------+---------+---------+-------------------------------------+
|   COL1  |   COL2  |   COL3  |      COALESCE(COL1, COL2, COL3)     |
+---------+---------+---------+-------------------------------------+
| a       | b       | c       | a                                   |
| a       | b       | NULL    | a                                   |
| a       | NULL    | NULL    | a                                   |
+---------+---------+---------+-------------------------------------+