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:

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.

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started