Snowflake COALESCE: Get the First Non-NULL Value
What it does
COALESCE returns the first non-NULL value it finds. However, it will return a NULL value if all the arguments are NULL.
This SQL function is particularly handy when you have multiple potential sources for a data point and you want to use the first available one.
Syntax
Here’s how you use COALESCE:
_10COALESCE(`<expr1>`, `<expr2>` [, ... , `<exprN>`])
where <expr1>, <expr2>, ..., <exprN>: A list of expressions or column names. COALESCE will return the first non-NULL value from this list.
Tips for using IFNULL
- Consistent data types: It's best to use arguments of the same type (all numbers, all strings, etc.) to avoid unexpected behavior or errors.
- Type coercion: If the arguments include numbers, Snowflake tries to convert all inputs to numbers. If the inputs are not compatible (like mixing text with numbers), you might need to explicitly cast them to match.
Examples
Handling missing data
Suppose you have a table of customer contacts where customers may provide either a home phone, a mobile phone, or both. You want to make sure you always have a contact number to reach them.
Input
Table: customer_contacts
| customer_id | home_phone | mobile_phone |
|---|---|---|
| 1 | null | 555-0199 |
| 2 | 555-0123 | null |
| 3 | null | null |
Snowflake SQL Query
_10SELECT_10 customer_id,_10 home_phone,_10 mobile_phone,_10 COALESCE(home_phone, mobile_phone, 'No Phone Available') AS contact_phone_10FROM_10 customer_contacts;
Output
| customer_id | home_phone | mobile_phone | contact_phone |
|---|---|---|---|
| 1 | null | 555-0199 | 555-0199 |
| 2 | 555-0123 | null | 555-0123 |
| 3 | null | null | No Phone Available |
