SQL CASE WHEN Producing NULL Instead of Expected Values: How to Fix

You write a CASE WHEN expression, run the query, and the result column is full of NULL. The logic seems right, the column names match, and yet nothing sticks. Before you rewrite the whole query, know that this failure mode has a short list of root causes — and every one of them is fixable once you know where to look.

What You'll Learn

• How SQL evaluates CASE WHEN expressions and when it falls through to NULL
• Why a missing ELSE clause is the most common silent culprit
• How NULL comparisons inside WHEN conditions always fail
• How data type mismatches between branches can collapse a result to NULL
• How whitespace, encoding issues, and aggregate context add extra traps

The Problem: CASE WHEN Returns NULL When It Shouldn't

Consider this query against a simple orders table:

SELECT
  order_id,
  status,
  CASE status
    WHEN 'shipped'  THEN 'In Transit'
    WHEN 'delivered' THEN 'Complete'
    WHEN 'pending'  THEN 'Waiting'
  END AS status_label
FROM orders;

If status_label is NULL for rows where status is clearly one of those three values, you have a mismatch between what the data actually contains and what your conditions test for. The expression didn't crash; it just quietly returned NULL.

How SQL Evaluates a CASE WHEN Expression

SQL evaluates CASE by testing each WHEN condition in order. The moment one condition is true, it returns the matching THEN value and stops. If no condition matches, it returns the value in the ELSE clause. If there is no ELSE clause, it returns NULL.

That last sentence is the source of most confusion. The standard implicitly adds ELSE NULL to every CASE expression that lacks an explicit ELSE. No error, no warning — just silent NULL output for every unmatched row.

There are two syntactic forms, and mixing them up is a common mistake:

-- Simple CASE: compares the expression to each WHEN value
CASE column_name
  WHEN 'value1' THEN 'result1'
  WHEN 'value2' THEN 'result2'
END

-- Searched CASE: evaluates each WHEN as an independent boolean condition
CASE
  WHEN column_name = 'value1' THEN 'result1'
  WHEN column_name IS NULL    THEN 'unknown'
END

The simple form cannot handle NULL checks or complex predicates. If you need either, use the searched form.

The Silent Killer: Missing ELSE Clause

This is the most common cause. Add an explicit ELSE to make unmatched rows visible instead of invisible:

SELECT
  order_id,
  status,
  CASE status
    WHEN 'shipped'   THEN 'In Transit'
    WHEN 'delivered' THEN 'Complete'
    WHEN 'pending'   THEN 'Waiting'
    ELSE 'UNMATCHED: ' || status  -- surface the actual value
  END AS status_label
FROM orders;

During debugging, return the raw value in the ELSE branch. You will immediately see what the column actually contains — often a value you didn't expect, like 'Shipped' with a capital S, or 'shipped ' with a trailing space.

Once you've fixed the underlying mismatch, replace the debug ELSE with whatever default makes sense for your use case. Never leave an implicit ELSE NULL in production code unless a NULL result is genuinely meaningful and intentional.

NULL Comparisons Inside WHEN Conditions

This trap catches nearly every SQL developer at least once. In SQL, NULL = NULL evaluates to NULL, not TRUE. That means this simple CASE will never match a NULL status:

-- This NEVER matches NULL rows
CASE status
  WHEN NULL THEN 'Unknown'
  ELSE status
END

The WHEN NULL branch uses an implicit equality comparison (status = NULL), which always evaluates to NULL — not TRUE. Switch to the searched form and use IS NULL:

-- Correct: use the searched form with IS NULL
CASE
  WHEN status IS NULL THEN 'Unknown'
  ELSE status
END

The same rule applies to IS NOT NULL, IN lists that include NULL, and any negated comparison. If the column you're testing can hold NULL, always use the searched form and handle NULL explicitly as the first WHEN branch — because once a row falls through the NULL check, every subsequent equality test will also fail for that row.

This behavior is closely related to a broader SQL NULL-handling problem. If you've run into NULL rows disappearing from aggregations, the article on SQL GROUP BY silently excluding NULLs covers the same underlying mechanic in a different context.

Data Type Mismatches Between WHEN Branches

SQL requires all THEN (and ELSE) expressions in a single CASE to resolve to a compatible type. When they don't, some databases silently coerce values and produce NULL for rows where coercion fails; others raise an error.

A common example: mixing integer and string branches.

-- Risky: mixing types across branches
CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 80 THEN 'B'
  WHEN score IS NULL THEN 0   -- integer, not a string
END AS grade

Databases vary in how they resolve this. PostgreSQL will raise a type error. MySQL may silently cast and return 0 as a string or NULL when the cast fails. Keep all branches the same type:

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 80 THEN 'B'
  WHEN score IS NULL THEN 'N/A'  -- string, consistent
  ELSE 'F'
END AS grade

If you genuinely need to mix types, cast explicitly using CAST(value AS target_type) in every branch so the database doesn't guess.

Type issues in SQL queries are a recurring theme. If your joins are also producing unexpected row counts, the article on SQL JOIN order silently changing row counts is worth reviewing alongside this one.

Whitespace and Trailing Characters Hiding Matches

Your WHEN 'shipped' condition looks right, but the stored value is 'shipped ' (one trailing space) or 'Shipped' (different casing). Both mismatches return NULL with no error.

Test for this by running a quick diagnostic:

SELECT
  status,
  LENGTH(status) AS raw_len,
  LENGTH(TRIM(status)) AS trimmed_len,
  LOWER(TRIM(status)) AS normalized
FROM orders
GROUP BY status, LENGTH(status), LENGTH(TRIM(status)), LOWER(TRIM(status));

If raw_len and trimmed_len differ for any row, you have hidden whitespace. If the same logical value appears with different cases, you have a case-sensitivity problem. Fix it at the source when you can; otherwise, normalize inside the CASE:

CASE LOWER(TRIM(status))
  WHEN 'shipped'   THEN 'In Transit'
  WHEN 'delivered' THEN 'Complete'
  WHEN 'pending'   THEN 'Waiting'
  ELSE 'UNMATCHED: ' || status
END AS status_label

Wrapping the tested expression in LOWER(TRIM(...)) handles both problems at once without touching the source data.

CASE Inside Aggregate Functions

Using CASE WHEN inside SUM() or COUNT() is a common pattern for conditional aggregation. It has its own NULL trap.

-- Conditional sum: count revenue only for completed orders
SELECT
  customer_id,
  SUM(CASE WHEN status = 'delivered' THEN amount END) AS delivered_revenue
FROM orders
GROUP BY customer_id;

When status is not 'delivered', the CASE returns NULL (no ELSE). SUM() ignores NULL values, so those rows contribute nothing to the total — which is exactly what you want here. So far, so good.

The trap appears when every row for a customer falls through to NULL: SUM() of all-NULL inputs returns NULL, not zero. If your downstream report treats NULL and zero differently (many do), wrap the result in COALESCE:

SELECT
  customer_id,
  COALESCE(
    SUM(CASE WHEN status = 'delivered' THEN amount END),
    0
  ) AS delivered_revenue
FROM orders
GROUP BY customer_id;

This pattern is especially important in dashboards and BI tools where a NULL cell can break calculations or display as blank. If you work in Power BI and see a similar blank-versus-zero issue in DAX measures, the article on Power BI DAX measures showing blank instead of zero covers the equivalent fix in that environment.

For COUNT, the behavior is slightly different: COUNT(expression) skips NULL values, while COUNT(*) counts all rows. Use COUNT(CASE WHEN ... THEN 1 END) for conditional counting — the ELSE NULL default makes NULL rows invisible to COUNT, which is the intended behavior.

Common Pitfalls Checklist

Before spending more time debugging, run through this list against your query:

• No ELSE clause: Add ELSE 'DEBUG: ' || column_name temporarily to surface unmatched values.
• NULL comparison using =: Switch to the searched form and use IS NULL as the first WHEN branch.
• Inconsistent branch types: Make all THEN and ELSE expressions the same data type, or cast explicitly.
• Hidden whitespace or casing: Check with LENGTH(TRIM(col)) != LENGTH(col), then normalize with LOWER(TRIM(col)).
• Wrong CASE form: Use the searched form (CASE WHEN col = val) rather than the simple form (CASE col WHEN val) whenever you need NULL checks or complex predicates.
• Aggregate returning NULL instead of zero: Wrap the outer aggregate in COALESCE(..., 0).
• Condition order matters: SQL stops at the first matching WHEN. If a broader condition appears before a narrower one, the narrower branch never fires. Order from most specific to least specific.

Condition ordering is worth a closer look. Consider this example where the order is wrong:

-- Bug: the first condition catches everything >= 50,
-- so the WHEN score >= 90 branch for an 'A' never fires
CASE
  WHEN score >= 50 THEN 'Pass'
  WHEN score >= 90 THEN 'Distinction'  -- dead code
  ELSE 'Fail'
END

-- Fixed: most specific condition first
CASE
  WHEN score >= 90 THEN 'Distinction'
  WHEN score >= 50 THEN 'Pass'
  ELSE 'Fail'
END

This doesn't produce NULL, but it produces wrong results by silently ignoring a branch — a closely related failure mode that the same debugging approach will reveal.

If you're encountering subtle result problems in other SQL expressions, the article on SQL DISTINCT vs GROUP BY giving different row counts illustrates how the SQL engine's evaluation order can produce surprising outputs in different contexts.

Wrapping Up: Next Steps

A CASE WHEN that returns NULL is always telling you something: either no condition matched, or a comparison failed silently. The fix is usually one of four things.

1. Add an explicit ELSE clause that exposes the raw value so you can see exactly what the data contains. Remove it once you've fixed the mismatch.
2. Switch to the searched CASE form and use IS NULL for any column that can hold NULL.
3. Normalize your comparison value with LOWER(TRIM(column_name)) to eliminate casing and whitespace mismatches without touching source data.
4. Align branch types and wrap outer aggregates in COALESCE(..., 0) wherever a NULL result would break downstream calculations.
5. Audit condition order to make sure narrower conditions appear before broader ones, so every branch has a chance to fire.

Once you internalize how SQL evaluates CASE — sequentially, stopping at first match, defaulting to NULL — most of these bugs become easy to spot before the query even runs.