SQL Boo-Boos #9: Why NULL Broke My Entire CASE Statement
Let me set the scene.
I had a bonus calculation query running in production for a client. It categorized employees into tiers based on their performance scores, and for weeks it had been working well without complaint.
Then someone from HR pinged me: “Hey, why do we have employees showing up with no tier assigned? They’re just… blank.”
I looked at the query. I looked at the data. I looked at the query again.
Everything looked fine to me. The CASE statement was airtight or so I thought. It wasn’t.
WHY YOU’D WRITE A CASE STATEMENT LIKE THIS IN THE FIRST PLACE
Before we get into the disaster, let me explain the totally reasonable thing I was trying to do because this is a pattern everyone writes at some point.
Imagine you have an employee performance table:
The score can be NULL for employees who are new, on leave, or whose review cycle hasn’t completed yet. You need to categorize them for the bonus report:
Score >= 90 → “Top Performer”
Score >= 75 → “Good Standing”
Score >= 60 → “Needs Improvement”
Anything else → “Underperformer”
Simple enough. So you write this:
SELECT
name,
performance_score,
CASE
WHEN performance_score >= 90 THEN 'Top Performer'
WHEN performance_score >= 75 THEN 'Good Standing'
WHEN performance_score >= 60 THEN 'Needs Improvement'
ELSE 'Underperformer'
END AS performance_tier
FROM employees;Looks clean. Looks logical. The ELSE is even there to catch anything that doesn’t match. You’re covered, right?
Run it, and here’s what you get:
Dave has no score. He’s new and his review hasn’t happened yet. But now HR’s report is calling him an “Underperformer” which is, to put it mildly, a problem.
THE THING NOBODY TOLD YOU ABOUT SQL LOGIC
Here’s what’s actually happening, and this is the bit that trips everyone up at least once.
Most of us grew up thinking in two-valued logic. Something is either TRUE or FALSE. That’s it. Binary. Clean.
SQL doesn’t work that way.
Get Preethi Kaluva’s stories in your inbox
Join Medium for free to get updates from this writer.
SQL uses THREE-valued logic:
That third state UNKNOWN is what you get whenever you try to compare anything with NULL.
Let me be really explicit about this:
NULL >= 90 → UNKNOWN (not FALSE)
NULL >= 75 → UNKNOWN (not FALSE)
NULL = NULL → UNKNOWN (not TRUE, not FALSE)
Every single WHEN condition in your CASE statement evaluates to UNKNOWN for Dave’s row. And here’s the crucial rule that makes this whole thing misbehave:
A WHEN condition only fires when its expression evaluates to TRUE.
UNKNOWN is not TRUE.
So SQL walks through your CASE statement like this:
WHEN performance_score >= 90 → UNKNOWN → skip
WHEN performance_score >= 75 → UNKNOWN → skip
WHEN performance_score >= 60 → UNKNOWN → skip
[ no conditions matched ]
ELSE → fires
The ELSE catches Dave. Not because he’s an underperformer. Not because any logic says he belongs there. But because ELSE is the fallback when nothing else matched and nothing matched because everything was UNKNOWN.
Here’s a way to think about it visually:
That ELSE clause isn’t your safety net. It’s a catch-all that doesn’t distinguish between “genuinely didn’t meet any condition” and “we literally couldn’t evaluate anything.”
HOW TO ACTUALLY FIX IT
The fix is simple once you understand what’s happening: you need to handle NULL explicitly, before your other conditions.
SELECT
name,
performance_score,
CASE
WHEN performance_score IS NULL THEN 'Not Yet Reviewed'
WHEN performance_score >= 90 THEN 'Top Performer'
WHEN performance_score >= 75 THEN 'Good Standing'
WHEN performance_score >= 60 THEN 'Needs Improvement'
ELSE 'Underperformer'
END AS performance_tier
FROM employees;
The IS NULL check uses the right tool for the job. IS NULL doesn’t do a comparison it checks the state of the value. So it returns TRUE for NULL, not UNKNOWN. Dave gets caught in the first WHEN, which fires correctly, and never reaches the ELSE.
Order matters here: the NULL check must come first. If it’s buried in the middle or at the end, you might still have UNKNOWN conditions evaluated before you get to it and depending on your logic, that can still cause problems.
So it is worth asking: does your ELSE mean “everything else” or “everything I thought of”? The difference lives in your NULL rows.
WRAPPING UP
NULL isn’t a value. It’s the absence of one. Compare anything against it and you don’t get TRUE or FALSE, you get UNKNOWN. CASE statements only act on TRUE, so UNKNOWN rows silently fall through every WHEN and land in ELSE, wearing whatever label you put there.
The three states to keep in your head:
TRUE → condition fires
FALSE → condition skipped, next one evaluated
UNKNOWN → condition skipped, exactly like FALSE but for a completely different reason
That last one is what gets people. UNKNOWN looks like FALSE from the outside. No error, no warning, data looks complete and somewhere in your output, a NULL row is labeled something it doesn’t deserve.
So check your CASE statements and put the IS NULL first….
