CASE WHEN: if-then logic inside a query
CASE WHEN condition THEN value … ELSE value END returns a value chosen by the first condition that is true, so a query can label, group or tidy values row by row.
Why it matters
Data often needs sorting into buckets — salary bands, "open" or "closed" orders, "new" or "returning" customers — and those labels aren't stored anywhere. CASE builds them inside the query. It is also the key to counting several things in one pass (Unit 2) and to pivoting.
The idea
CASE is SQL's if-else. For each row, it checks the WHEN conditions from top to bottom and returns the THEN value of the first one that is true. If none is true, it returns the ELSE value — or NULL if there is no ELSE.
CASE is an expression, so it can go anywhere a value can: in SELECT, WHERE, ORDER BY, GROUP BY, or inside SUM and COUNT. The short form, CASE status WHEN 'shipped' THEN …, compares one column with several values.
Sorting post into pigeonholes. You read the address, check the first rule ("London?"), then the next ("Leeds?"), and drop the letter into the first hole that fits. Anything that fits no rule goes into "other" — that is ELSE.
A postal worker can put one letter in two holes by mistake. CASE never does: it stops at the first true WHEN, so the order of the WHENs matters. Put the strictest test first.
Syntax
CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE other_value
END AS new_columnHow it works
- For Asha (95000):
salary >= 80000is true, so the band is 'high', and CASE stops. - For Ben (70000): the first test is false;
salary >= 50000is true, so 'medium'. - For Eli (45000): both tests are false, so ELSE gives 'low'.
- If the tests were in the other order (>= 50000 first), Asha would wrongly get 'medium'. Order matters.
- END closes the CASE, and AS names the new column.
Key points
- CASE returns the THEN value of the first true WHEN, then stops.
- No ELSE means NULL for rows that match nothing.
- Always finish with END, and name the result with AS.
- CASE is an expression: it works in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates.
- Every THEN should return the same type — all text, or all numbers.
Common mistakes
- Putting the loose condition first (
>= 50000before>= 80000), so the strict one is never reached. - Forgetting END, which gives a syntax error.
- Writing
CASE status WHEN NULL THEN …. The short form uses =, so it never matches NULL. UseCASE WHEN status IS NULL THEN ….
Not true: The order of the WHEN branches in a CASE makes no difference to the result.
In fact: CASE stops at the first WHEN that is true. With >= 50000 written before >= 80000, the second branch is never reached: put the strictest test first.
In the interview
CASE is SQL's conditional expression: it evaluates WHEN conditions in order and returns the first matching THEN value, or the ELSE value, or NULL if there is no ELSE. Because it is an expression, it works anywhere. I use it to bucket values and to sort in a custom order. Most of all I use it inside aggregates, like SUM of CASE WHEN status equals delivered THEN 1, to count several categories in a single pass.
Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.