What does a CASE expression return when no WHEN matches and there is no ELSE?
answer
- The clause you left out still has a meaning
- Not an empty string, not zero
- The row is not removed
- It behaves like any missing value
- Same as writing ELSE NULL
basics
~20 sIt returns NULL. An omitted ELSE is defined as ELSE NULL, so any row matching no WHEN branch gets NULL rather than an empty string, a zero, or an error — and the row itself is still returned.
solid answer
~40 sThe standard defines a `CASE` with no `ELSE` as if `ELSE NULL` were written, so rows that satisfy none of the `WHEN` conditions produce **NULL**. Two consequences bite in practice. First, a derived column silently becomes nullable: `CASE WHEN qty > 10 THEN 'bulk' END` yields NULL for every small order, which then propagates through concatenation, comparison and sorting like any other NULL. Second, the row is *not* filtered out — CASE is an expression that computes a value, not a predicate that removes rows. Because of this I write an explicit `ELSE` whenever a default is meaningful, and I keep the implicit NULL only when "no category applies" is genuinely the intent — for example when the value feeds something that should ignore unmatched rows.
code
sql · 10 lines-- no ELSE: unmatched rows get NULL, and they are still returned
SELECT qty,
CASE WHEN qty > 10 THEN 'bulk' END AS label
FROM order_lines;
-- qty = 3 -> label IS NULL
-- explicit default makes the fallback visible
SELECT qty,
CASE WHEN qty > 10 THEN 'bulk' ELSE 'single' END AS label
FROM order_lines;go deeper
Memorise the one-line rule: no ELSE means ELSE NULL, and the row still comes back. Be ready to predict the output of a two-branch CASE over sample values.
Explain how that NULL then propagates — comparisons going UNKNOWN, concatenation collapsing, rows grouping into one unnamed bucket — and when you would write an explicit default instead.
Show that you treat a missing ELSE as a data-quality risk: new codes appearing in production quietly become NULL categories, so you make unmapped values visible rather than absent.
Frame it as a contract question — whether a derived category column is nullable is part of the interface a report or downstream table depends on, and it should be decided once, not per query.
## The rule A `CASE` expression is evaluated by testing its `WHEN` branches in order. If one is satisfied, its `THEN` result is the value of the whole expression. If **none** is satisfied, the `ELSE` result is used — and when the `ELSE` clause is omitted, the standard specifies the expression as though `ELSE NULL` had been written. So: ```sql SELECT qty, CASE WHEN qty > 10 THEN 'bulk' END AS label FROM order_lines; ``` For `qty = 3`, `label` is NULL. Not `''`, not `'0'`, not an error, and the row is still in the result. ## Why people are surprised Three different mental models collide here. **"It should be an empty string."** Many host languages return an empty or default value from an unmatched conditional. SQL does not: NULL is the language's marker for *no value*, and it is the only sensible neutral result when the branches produce dates, numbers or text alike. **"The row disappears."** CASE is a **value expression**, not a predicate. Putting one in the SELECT list computes a column; it never removes rows. Only `WHERE` and `HAVING` remove rows. (A CASE *can* appear inside a WHERE predicate, but then the comparison around it does the filtering, not the CASE itself.) **"An unmatched value falls through to the controlling expression."** It does not. `CASE status WHEN 'N' THEN 'new' END` returns NULL — not `status` — for `status = 'S'`. If you want an unmatched value passed through unchanged, say so: `ELSE status`. ## Where the implicit NULL leaks Once a NULL is in the result, every ordinary NULL rule applies to it downstream, which is where a small omission becomes a visible bug: - **Comparison.** `WHERE label = 'bulk'` drops the NULL rows, because a comparison with NULL is UNKNOWN, not TRUE. Filtering on a CASE-derived column therefore silently loses the unmatched rows. - **Concatenation and arithmetic.** In standard SQL, combining NULL with anything yields NULL, so a report line built by concatenating a CASE result can collapse to nothing. - **Sorting.** NULLs cluster at one end of an `ORDER BY`, and which end differs by engine, so a category column with implicit NULLs sorts inconsistently across systems. - **Grouping.** All the NULL rows collapse into a single group, which reads as an unlabelled bucket in the output. - **Client display.** Application layers frequently render NULL as an empty cell or throw on a null unbox, so "no ELSE" often surfaces first as a bug two layers away from the query. ## Writing it deliberately The fix is to decide, per query, whether "none of the above" has a name: ```sql -- explicit default: every row gets a label CASE WHEN qty > 10 THEN 'bulk' ELSE 'single' END -- explicit catch-all for an open set of codes CASE status WHEN 'N' THEN 'new' WHEN 'S' THEN 'shipped' ELSE 'other' END -- deliberate NULL, written out so the reader sees the intent CASE WHEN score >= 90 THEN 'top' ELSE NULL END ``` Writing `ELSE NULL` explicitly changes nothing semantically, but it documents that the NULL was chosen rather than forgotten — a small readability win in a long ladder of branches. ## A related trap: the missing catch-all A CASE over a code column that enumerates today's known codes will keep working silently when a new code appears in the data — new codes just start producing NULLs. If a category column must never be NULL, an `ELSE` that produces a visible marker (`'UNMAPPED'`) turns a silent hole into something a report reviewer can see, and it keeps the column non-nullable for downstream consumers. ## The result-type caveat Because the omitted `ELSE` contributes a NULL, the CASE expression is nullable even if every `THEN` branch returns a non-null value. If you insert that expression into a `NOT NULL` column, the insert fails at runtime for exactly the rows that matched nothing — another reason to make the default explicit at the point where the logic is written rather than at the point where it breaks.
- Does an unmatched CASE remove the row from the result set?No. CASE is a value expression: it computes a column value, so an unmatched row is returned with NULL in that column. Only WHERE and HAVING remove rows. If you also want those rows gone, add a predicate — but note that filtering on the CASE-derived column with `= 'bulk'` drops NULLs as a side effect, which is a different thing from filtering deliberately.
- Does a simple CASE fall back to its controlling expression when nothing matches?No. `CASE status WHEN 'N' THEN 'new' END` returns NULL for any other status, not the original `status` value. If you want unmatched values passed through, write it: `ELSE status`. Assuming pass-through is a common source of columns that go mysteriously empty for exactly the values you did not enumerate.
- Is there any reason to write ELSE NULL explicitly?Semantically none — it is what an omitted ELSE already means. It is a readability choice: in a long branch ladder it tells the next reader the NULL was intended rather than forgotten, which matters in code review and when someone later adds a branch. Some teams enforce it so that every CASE ends with a visible default.
saying these in an interview costs you the question
- Saying an unmatched CASE returns an empty string
- Claiming the row is filtered out of the result
- Expecting a simple CASE to return its controlling expression
- Assuming a CASE column is never nullable
- Thinking a missing ELSE raises an error at runtime