Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 COALESCE with no arguments, which causes an error.
  • Expecting COALESCE to evaluate expressions beyond the first non-NULL value (it stops at the first non-NULL).
  • Confusing COALESCE with IFNULL, 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

FeatureDescription
PurposeReturns first non-NULL value from arguments
Minimum ArgumentsTwo or more expressions
ReturnsValue of first non-NULL expression
Stops EvaluatingAfter first non-NULL found
Use CaseReplace 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.