How to Use COALESCE Function in Snowflake: Syntax and Examples
In Snowflake, use the
COALESCE function to return the first non-NULL value from a list of expressions. It helps replace NULLs with a default or fallback value easily in queries.Syntax
The COALESCE function takes two or more expressions and returns the first one that is not NULL.
- expression1, expression2, ...: Values or columns to check in order.
sql
COALESCE(expression1, expression2, ...)Example
This example shows how COALESCE returns the first non-NULL value from the given columns or values.
sql
SELECT COALESCE(NULL, NULL, 'apple', 'banana') AS first_non_null, COALESCE(NULL, 'orange', 'grape') AS first_non_null_fruit, COALESCE(NULL, NULL, NULL, 'default') AS fallback_value;
Output
first_non_null | first_non_null_fruit | fallback_value
---------------|----------------------|---------------
apple | orange | default
Common Pitfalls
Common mistakes include:
- Using
COALESCEwith no arguments, which causes an error. - Expecting
COALESCEto evaluate expressions beyond the first non-NULL value (it stops at the first non-NULL). - Confusing
COALESCEwithIFNULL, which only takes two arguments.
sql
/* Wrong: No arguments */ -- SELECT COALESCE(); -- This will cause an error /* Right: At least two arguments */ SELECT COALESCE(NULL, 'value') AS result;
Quick Reference
| Feature | Description |
|---|---|
| Purpose | Returns first non-NULL value from arguments |
| Minimum Arguments | Two or more expressions |
| Returns | Value of first non-NULL expression |
| Stops Evaluating | After first non-NULL found |
| Use Case | Replace NULLs with default values |
Key Takeaways
COALESCE returns the first non-NULL value from its arguments in order.
Always provide at least two arguments to COALESCE to avoid errors.
COALESCE stops checking once it finds a non-NULL value.
Use COALESCE to handle NULLs and provide fallback values in queries.
COALESCE differs from IFNULL by supporting multiple arguments.
