How to Handle NULL Values in Snowflake

How do I handle NULL values in Snowflake SQL?

Shows how to detect, replace, and transform NULL values in Snowflake SQL using built-in functions.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why do NULL values appear in Snowflake?

NULL represents missing or unknown data. It surfaces when a column value is absent, a calculation returns no result, or data is loaded with empty fields.

Which functions replace or inspect NULLs?

Snowflake offers NVL, COALESCE, IFNULL, NULLIF, and NVL2. These functions let you swap, detect, or transform NULL values in SELECT, WHERE, and JOIN clauses.

How to use NVL?

NVL(expression, replacement) returns replacement when expression is NULL.Use NVL(o.total_amount, 0) to avoid NULLs in revenue reports.

When is COALESCE better?

COALESCE checks each argument sequentially and returns the first non-NULL. Helpful when you have multiple fallback columns such as p.sale_price, p.price, 0.

What does NULLIF do?

NULLIF(expr1, expr2) returns NULL when the two expressions match; otherwise it returns expr1. Convert sentinel values like 0 or '' into NULL for cleaner analytics.

How does NVL2 add flexibility?

NVL2(expr, value_if_not_null, value_if_null) chooses between two outputs based on whether expr is NULL.This avoids an extra CASE expression.

How to filter NULLs in WHERE clauses?

Use IS NULL or IS NOT NULL. Comparisons with '=' or '!=' never match NULL, leading to empty result sets.

Best practices for NULL handling

Standardize default replacements, keep data types aligned, document business meaning of NULLs, and audit NULL counts to spot data quality issues.

Why How to Handle NULL Values in Snowflake is important

How to Handle NULL Values in Snowflake Example Usage


SELECT
    o.id                AS order_id,
    COALESCE(o.total_amount, 0)              AS charged_amount,
    NVL(p.stock, 0)                          AS current_stock,
    NULLIF(c.email, '')                      AS normalized_email,
    NVL2(o.total_amount, 'Paid', 'Unpaid')   AS payment_state
FROM Orders      o
JOIN Customers   c ON c.id = o.customer_id
JOIN OrderItems  oi ON oi.order_id = o.id
JOIN Products    p ON p.id = oi.product_id;

How to Handle NULL Values in Snowflake Syntax


-- Replace NULL with 0 when reporting revenue
SELECT NVL(o.total_amount, 0) AS total_amount
FROM Orders o;

-- Choose first non-NULL among several price columns
SELECT COALESCE(p.sale_price, p.price, 0) AS final_price
FROM Products p;

-- Turn empty strings into NULL during load
COPY INTO staging_table
FROM @mystage/file.csv
NULL_IF=('','NULL');

-- Convert sentinel value 0 to NULL
SELECT NULLIF(o.total_amount, 0) AS total_or_null
FROM Orders o;

-- Conditional replacement using NVL2
SELECT NVL2(o.total_amount, 'Paid', 'Pending') AS payment_status
FROM Orders o;

Common Mistakes

Using '=' or '!=' to compare with NULL. These operators never match NULL, resulting in unexpected empty sets. Fix by using IS NULL or IS NOT NULL.
Mixing data types in COALESCE, e.g., COALESCE(price, 'N/A'). Snowflake converts all arguments to a common type, which may fail. Cast or keep types consistent.

Frequently Asked Questions (FAQs)

How do I count NULLs in a column?

Use COUNT(*) - COUNT(column) to get the number of NULL rows because COUNT(column) ignores NULLs.

Can I convert blanks to NULL during file loads?

Yes. Add NULL_IF=('','NULL') to your COPY INTO command so Snowflake maps empty strings or the word "NULL" to actual NULL values.

Does COALESCE slow down queries?

COALESCE is a lightweight expression. Performance impact is negligible compared with I/O and join costs. Keep predicates selective to minimize scanned data.