Snowflake
Handle Missing Values

Snowflake IFNULL: Handle Missing Values Conditionally

What it does

IFNULL looks at two values. If the first value is missing (NULL), IFNULL uses the second value as a substitute. If the first value is present, IFNULL just uses it as it is.

Syntax

Here's how you write it:


_10
IFNULL(`<expr1>`, `<expr2>`)

where:

  • <expr1>: The main value or column you want to check.
  • <expr2>: The backup value or column you want to use if the main value is missing.

When to use IFNULL

  • Filling in blanks: Use IFNULL to make sure there are no blanks in your data, which can make analysis and reporting easier and prevent errors in calculations.
  • Data cleanup: Great for cleaning up data, especially if you're merging data from different sources where some information might be missing.

Tips for using IFNULL

  • Consistent data types: Try to use the same type of data for both expressions in IFNULL to avoid errors or unexpected behavior, especially when dealing with numbers or dates.
  • Avoid complex conversions: If you’re working with numbers and strings together, make sure to consider how they interact. Sometimes you might need to explicitly convert types to match each other.

Examples

Replace NULL values with the Unassigned string value

Imagine a database tracking various characters from "The Hitchhiker's Guide to the Galaxy" and their associated spacecraft. Some characters, however, might not have a spacecraft assigned to them. We'll use the IFNULL function to ensure every character shows up with a spacecraft name, even if it's just a placeholder.

Input

Table: characters

character_idnamespacecraft
1Arthur DentHeart of Gold
2Ford Prefectnull
3Zaphod BeeblebroxHeart of Gold
4Trilliannull
5MarvinHeart of Gold

Snowflake SQL Query


_10
SELECT
_10
character_id,
_10
name,
_10
IFNULL (spacecraft, 'Unassigned') AS spacecraft
_10
FROM
_10
characters;

Output

character_idnamespacecraft
1Arthur DentHeart of Gold
2Ford PrefectUnassigned
3Zaphod BeeblebroxHeart of Gold
4TrillianUnassigned
5MarvinHeart of Gold
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free