Why does CASE status WHEN NULL THEN 'unknown' ELSE status END never return 'unknown'?

## What the simple form actually means The SQL standard defines the simple CASE as shorthand. Writing ```sql CASE status WHEN 'N' THEN 'new' WHEN NULL THEN 'unknown' ELSE status END ``` is defined to mean ```sql CASE WHEN status = 'N' THEN 'new' WHEN status = NULL THEN 'unknown' ELSE status END ``` Once the shorthand is expanded the behaviour is obvious. `status = NULL` is a comparison with the null value, and in SQL a comparison where either side is NULL evaluates to **UNKNOWN** — not TRUE, not FALSE. A `WHEN` branch is selected only when its condition is TRUE. UNKNOWN is not TRUE, so the branch is unreachable for every row, whatever `status` holds. The expression is nevertheless perfectly legal syntax: no engine rejects it, no warning appears, and the query runs. It is dead code that looks like a null-handling branch, which is why it survives review. ## What a NULL row actually gets A row with `status IS NULL` fails every equality branch (they are all UNKNOWN) and lands on `ELSE`. In the example above `ELSE status` returns the NULL itself, so the column shows nothing — exactly the outcome the author was trying to prevent. If there were no `ELSE`, the implicit `ELSE NULL` would give the same visible result by a different route. Either way, the label never appears. ## The fix Switch to the searched form and use the null **predicate**: ```sql SELECT CASE WHEN status IS NULL THEN 'unknown' WHEN status = 'N' THEN 'new' WHEN status = 'S' THEN 'shipped' ELSE 'other' END AS status_label FROM orders; ``` `IS NULL` is a predicate that returns TRUE or FALSE — never UNKNOWN — so the branch fires. Put it **first** in the ladder: it costs nothing and it makes the null case visible at the top of the decision list, before any branch that might otherwise absorb those rows. Where the standard's `IS NOT DISTINCT FROM` comparison is available, `CASE WHEN status IS NOT DISTINCT FROM 'N' THEN ...` gives null-safe equality (two NULLs compare as equal, and NULL versus a value as unequal), which is occasionally handy when you are comparing two nullable expressions rather than testing for null. Support differs between engines, so check yours before relying on it in portable code. ## Why this generalises The same rule drives several superficially different traps in SQL, and recognising the shared cause is what an interviewer is really testing: - `WHERE status = NULL` returns no rows. - `WHERE status <> 'N'` silently excludes rows where `status` is NULL, because the comparison is UNKNOWN. - A join predicate `a.key = b.key` never matches two NULL keys. All of them come from the single fact that comparison with NULL yields UNKNOWN and only TRUE passes. The simple CASE trap is the same rule wearing different syntax — which is exactly why expanding the shorthand in your head is the fastest way to reason about it. ## The mirror-image trap The reverse mistake also appears: assuming a NULL controlling expression makes the whole CASE return NULL. It does not. `CASE status WHEN 'N' THEN 'new' ELSE 'other' END` returns `'other'` for a NULL status, because the NULL simply fails every equality test and reaches `ELSE`. So a null value is not propagated — it is silently *categorised as the default*, which can be worse than a NULL because it looks like real data. When "missing" and "anything else" are genuinely different categories, you must add the `IS NULL` branch; the default will not do it for you. ## Reviewing for it Two cheap habits catch this class of bug. First, treat any literal `NULL` appearing directly after `WHEN` in a simple CASE as an automatic defect — there is no correct use of it. Second, when a CASE assigns categories over a nullable column, check the counts: run a grouped count of the derived label and confirm the null bucket's size matches `SELECT COUNT(*) FROM t WHERE col IS NULL`. If the null rows are hiding inside your default category, the numbers will say so.