CASE WHEN with NULL Handling in SQL

Overview

The SQL CASE expression is the Swiss-army knife of conditional logic in relational databases. Yet many practitioners struggle when NULL values enter the picture. Because NULL represents an unknown rather than an empty string or zero, comparisons that seem intuitive often return unexpected results. This article walks through the nuances of writing a CASE WHEN statement that safely handles NULLs, provides best practices, demonstrates real-world patterns, and highlights common pitfalls.

Why NULL Handling Matters

Incorrect NULL handling can silently corrupt business logic and analytics. A financial report that treats NULL revenue as zero will understate revenue, while a health-care application that misclassifies NULL test results may trigger false alarms. Because CASE is frequently used to derive new fields, cleanse data, and build dimensional attributes, mastering NULL-aware patterns is critical for:

• Accurate aggregations and KPIs
• Data quality and governance
• Application logic embedded in SQL
• Performance—one well-written CASE beats multiple nested queries

Key Concepts

The Three-Valued Logic

SQL operates under three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL yields UNKNOWN, which acts like FALSE inside a CASE WHEN branch. For example:

SELECT CASE WHEN NULL = 'foo' THEN 'match' END; -- returns NULL

IS [NOT] NULL Predicate

The only safe way to test for NULL is IS NULL (or IS NOT NULL). Never rely on equality or inequality operators.

COALESCE and IFNULL

COALESCE(expr1, expr2, ...) returns the first non-NULL expression, making it ideal for defaulting NULL to a sentinel value before comparison.

Simple vs. Searched CASE

• Simple CASE: CASE expression WHEN value1 THEN ...
• Searched CASE: CASE WHEN condition THEN ...
Use searched CASE when you need IS NULL checks.

NULL-Safe CASE Patterns

Pattern 1: Explicit IS NULL Branch

CASE
WHEN amount IS NULL THEN 'Unknown'
WHEN amount < 0 THEN 'Negative'
WHEN amount = 0 THEN 'Zero'
ELSE 'Positive'
END AS amount_category

By checking amount IS NULL first, you guarantee correct classification.

Pattern 2: COALESCE Defaulting

CASE
WHEN COALESCE(status, 'PENDING') = 'PENDING' THEN 0
ELSE 1
END AS is_complete

Here NULL is transformed into 'PENDING' before comparison, keeping the CASE logic simple.

Pattern 3: NULL-Aware Equality (MySQL)

Some engines such as MySQL offer <=> (NULL-safe equal). Use cautiously because it is non-standard.

Pattern 4: CASE in Aggregations

SUM(CASE WHEN return_date IS NULL THEN 1 ELSE 0 END) AS outstanding_orders

Counting NULLs accurately is a common requirement for service-level metrics.

Step-by-Step Example

Suppose we have an orders table:

order_id | total_amount | shipped_at
-------- | ----------- | ----------
1 | 125.00 | 2024-01-08
2 | NULL | NULL
3 | -20.00 | 2024-02-01

We want a human-readable status:

SELECT order_id,
CASE
WHEN shipped_at IS NULL AND total_amount IS NULL THEN 'Pending & Amount Unknown'
WHEN shipped_at IS NULL THEN 'Pending Shipment'
WHEN total_amount < 0 THEN 'Refunded'
ELSE 'Completed'
END AS order_status
FROM orders;

The query correctly handles NULLs in both shipped_at and total_amount.

Best Practices

• Always address NULL first in the CASE branch order. Once a TRUE condition matches, subsequent checks are skipped.
• Prefer searched CASE with explicit predicates; simple CASE implicitly uses = which fails on NULL.
• Default with COALESCE when the substitute value is logically sound.
• Avoid over-nesting CASE—complexity breeds NULL mistakes. Consider CTEs or lateral joins for readability.
• Unit-test your CASE logic with rows that contain NULLs. Automated tests catch silent failures.

Common Mistakes

Mistake 1: Using = NULL

CASE WHEN column = NULL THEN 'Bad' END -- Always NULL

Fix: Replace with IS NULL.

Mistake 2: Missing ELSE Clause

Without ELSE, unmatched rows return NULL by default. Add an ELSE or ensure all possibilities are handled.

Mistake 3: Misplaced Branch Order

Putting broad conditions before NULL checks causes NULL rows to fall through unexpectedly. Order conditions from most specific to most general.

Galaxy and NULL-Aware CASE

Galaxy’s modern SQL editor highlights NULL logic errors with inline linting. Type CASE WHEN col = NULL and the AI copilot suggests replacing with IS NULL. Collections let teams endorse the corrected query, ensuring company-wide best practices.

Conclusion

Handling NULLs in a CASE WHEN statement is less about syntax and more about logical precision. Use searched CASE expressions, test vigorously, and leverage tooling like Galaxy to spot errors early. Master these patterns once, and you will prevent a class of silent, hard-to-trace bugs in every analytics or application stack you touch.