Snowflake
Get the First Non-NULL value

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:


_10
COALESCE(`<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_idhome_phonemobile_phone
1null555-0199
2555-0123null
3nullnull

Snowflake SQL Query


_10
SELECT
_10
customer_id,
_10
home_phone,
_10
mobile_phone,
_10
COALESCE(home_phone, mobile_phone, 'No Phone Available') AS contact_phone
_10
FROM
_10
customer_contacts;

Output

customer_idhome_phonemobile_phonecontact_phone
1null555-0199555-0199
2555-0123null555-0123
3nullnullNo Phone Available
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free