CASE WHEN Statement for NULLs in SQL

Handling NULL values correctly is critical for any data professional. A single unhandled NULL can distort aggregates, hide data-quality issues, or even break application logic. The most flexible, ANSI-standard way to deal with NULLs is the CASE WHEN expression. This article dives deep into how to use CASE WHEN specifically for NULL handling, demonstrates cross-vendor syntax, and offers best practices you can apply today in any modern SQL editor—especially Galaxy.

Why NULL Handling Matters

In SQL, NULL represents an unknown or missing value. Arithmetic with NULL propagates that unknown state, leading to unexpected results—e.g., 5 + NULL = NULL. Aggregations like AVG skip NULLs entirely. Filtering on equality also behaves differently because NULL = NULL is not TRUE; it is UNKNOWN. An explicit strategy is therefore mandatory in production queries, data pipelines, dashboards, and ELT jobs.

CASE WHEN Basics

The ANSI-SQL CASE expression comes in two forms:

• Searched CASE: CASE WHEN condition THEN value [ ... ] ELSE value END
• Simple CASE: CASE expression WHEN value THEN result [ ... ] ELSE result END

For NULL handling we almost always use the searched form, because we need to test with IS NULL or IS NOT NULL.

Simple NULL Replacement

SELECT
customer_id,
CASE WHEN phone IS NULL THEN 'No Phone' ELSE phone END AS phone_clean
FROM dim_customer;

This replaces NULL with the string 'No Phone'. Functionally, it is equivalent to COALESCE(phone,'No Phone'), but CASE WHEN gives you more room for multiple conditions, as we will see next.

Multi-Branch Logic with NULL Checks

SELECT
order_id,
CASE
WHEN shipped_date IS NULL AND cancelled_date IS NULL THEN 'Pending'
WHEN shipped_date IS NULL AND cancelled_date IS NOT NULL THEN 'Cancelled'
WHEN shipped_date IS NOT NULL THEN 'Shipped'
ELSE 'Unknown'
END AS order_status
FROM fct_orders;

Here we combine NULL checks with additional predicates to classify orders without creating a nested IF tree in application code.

Using CASE WHEN with Aggregates

Because SUM() ignores NULLs, you can count or sum conditional subsets safely inside an aggregate:

SELECT
COUNT(*) AS total_rows,
SUM(CASE WHEN churn_date IS NOT NULL THEN 1 ELSE 0 END) AS churned_users
FROM dim_user;

The CASE ensures you transform a NULL into a 0 or 1 so the SUM behaves predictably.

Vendor Variations

PostgreSQL / Redshift / Snowflake

CASE WHEN col IS NULL THEN 'missing' END

BigQuery (StandardSQL)

CASE WHEN col IS NULL THEN 'missing' END

SQL Server (T-SQL)

CASE WHEN col IS NULL THEN 'missing' END

Because CASE is ANSI-standard, the syntax is identical across most engines. Differences appear only in helper functions (e.g., ISNULL in SQL Server), but CASE remains portable.

Best Practices

• Be explicit: Always write ELSE. Leaving it out silently returns NULL, which defeats the purpose of the exercise.
• Avoid deep nesting: If your CASE has more than ~5 branches, consider refactoring into a dimensional table you can JOIN on.
• Keep types consistent: Every THEN and the ELSE must return the same or coercible data type, or the query will error (or worse, implicitly cast).
• Document intent: Use comments or expressive literal values ('No Phone' vs. '') so downstream consumers understand why the NULL was replaced.
• Profile data before assuming: Don’t just blanket-replace NULL with 0; validate that 0 makes sense in the business context.

Common Pitfalls

1. Using = NULL Instead of IS NULL

Because NULL is not a value, = comparisons do not work. Always use IS NULL or IS NOT NULL.

2. Forgetting the ELSE Clause

Leaving off ELSE returns NULL when none of the earlier conditions match, often causing silent data loss.

3. Implicit Casting Errors

If one branch returns an INTEGER and another a VARCHAR, engines like Presto will raise an error or choose an unwanted super-type.

Galaxy Accelerates NULL Handling

In Galaxy’s desktop SQL editor, you can:

• Trigger context-aware autocomplete after typing CASE to insert a template that includes ELSE by default.
• Highlight columns with high NULL ratios in the schema sidebar—making it obvious where to add CASE WHEN logic.
• Use the AI Copilot to refactor existing queries; simply ask, “Replace all NULL shipping dates with ‘Pending’.”
• Share and endorse your finalized query via Collections so the whole team reuses consistent NULL handling.

Conclusion

A well-written CASE WHEN expression is the Swiss-army knife of NULL handling.

Whether you are producing a KPI dashboard, feeding a machine-learning model, or cleaning data in an ELT pipeline, mastering this pattern prevents silent errors and improves data quality. With Galaxy’s AI-powered editor, writing and sharing these expressions is faster than ever.