CASE is SQL’s if/else, but it’s an expression, not a statement. It produces one value per row, and you can put it anywhere a value is allowed: the select list, ORDER BY, GROUP BY, inside SUM(), even in an UPDATE ... SET. Interviewers like it because a two-line CASE can expose whether a candidate really understands evaluation order and NULL.
All the queries below run against a small orders table:
CREATE TABLE orders (
id integer PRIMARY KEY,
status text,
amount numeric,
region text
);
INSERT INTO orders VALUES
(1, 'paid', 1500, 'eu'),
(2, 'paid', 100, 'eu'),
(3, 'refunded', 99.99,'us'),
(4, 'pending', 1000, 'us'),
(5, NULL, 40, 'eu'),
(6, 'paid', 250, 'us');The output shown in this article comes from running these exact statements in SQLite 3.53, which handles CASE and FILTER the same way PostgreSQL does for this data. Where PostgreSQL behaves differently, the text says so.
Two spellings of the same idea
The searched form evaluates independent boolean conditions:
CASE
WHEN amount >= 1000 THEN 'high'
WHEN amount >= 100 THEN 'medium'
ELSE 'low'
ENDThe simple form compares one expression against a list of values:
CASE status
WHEN 'paid' THEN 'ok'
WHEN 'refunded' THEN 'money back'
ELSE 'other'
ENDThe simple form is shorthand for CASE WHEN status = 'paid' THEN .... That detail matters later, because the = hidden inside it is where most NULL bugs come from. Use the simple form for a lookup on exact values and the searched form for everything else: ranges, IS NULL, LIKE, conditions spanning several columns.
Order of branches decides the answer
PostgreSQL tests the WHEN clauses from top to bottom and stops at the first one that’s true. Here’s the banding query with its result:
SELECT id, amount,
CASE
WHEN amount >= 1000 THEN 'high'
WHEN amount >= 100 THEN 'medium'
ELSE 'low'
END AS band
FROM orders
ORDER BY amount DESC; id | amount | band
----+--------+--------
1 | 1500 | high
4 | 1000 | high
6 | 250 | medium
2 | 100 | medium
3 | 99.99 | low
5 | 40 | lowOrder 1 is 1500, which also satisfies amount >= 100, but it never gets that far. Now swap the two WHEN lines, a mistake that’s easy to make when someone adds a new band to the bottom of an existing list:
id | amount | wrong
----+--------+--------
1 | 1500 | medium
4 | 1000 | medium
6 | 250 | medium
2 | 100 | medium
3 | 99.99 | low
5 | 40 | lowNo error, no warning, and the high band is gone. The broader condition swallowed the narrower one. When you write overlapping ranges, put the most restrictive test first, or make the ranges disjoint (amount >= 100 AND amount < 1000) so their order stops mattering. Disjoint ranges cost a few extra characters and survive a careless reorder.
Boundaries deserve a second look too. Is an order of exactly 100 medium or low? With >= it’s medium, and order 2 proves it. If the product team says “over 100”, that’s >, and the row moves. Testing the exact boundary value is the cheapest check you can run on a CASE.
What happens when nothing matches
Leave out ELSE, and any row that matches no branch gets NULL. Sometimes that’s what you want. More often it’s an accident that surfaces somewhere else: a dashboard filter WHERE band <> 'low' drops the NULL rows because NULL <> 'low' is unknown, not true; a SUM skips them; a NOT NULL column rejects the insert.
The simple form has a sharper version of the same trap. This query tries to label missing statuses:
SELECT id, status,
CASE status
WHEN 'paid' THEN 'ok'
WHEN NULL THEN 'unknown'
END AS s
FROM orders
ORDER BY id; id | status | s
----+----------+------
1 | paid | ok
2 | paid | ok
3 | refunded | NULL
4 | pending | NULL
5 | NULL | NULL
6 | paid | okRow 5 doesn’t get 'unknown'. The simple form compares status = NULL, and any comparison with NULL yields NULL, which CASE treats as not true. Rows 3 and 4 get NULL for a different reason: they fell off the end without an ELSE. Two different causes, one identical-looking output, which is why this is hard to debug from the result alone.
The fix uses the searched form, which lets you write IS NULL:
CASE
WHEN status IS NULL THEN 'unknown'
ELSE status
ENDFor that particular pattern, COALESCE(status, 'unknown') says the same thing more briefly, and you’ll often see reviewers suggest it.
Counting several things in one pass
The most valuable use of CASE in reporting is conditional aggregation. Suppose you need paid and refunded counts per region. You could run two queries, or UNION two grouped queries, but one scan does it:
SELECT region,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_too,
SUM(CASE WHEN status = 'paid' THEN amount END) AS paid_amount
FROM orders
GROUP BY region
ORDER BY region; region | paid | paid_too | paid_amount
--------+------+----------+-------------
eu | 2 | 2 | 1600
us | 1 | 1 | 250The two counting styles agree because COUNT(expr) counts non-NULL values, and the CASE without ELSE returns NULL for every row that isn’t paid. That’s the one place where a missing ELSE is a deliberate tool rather than a bug.
Watch the sum, though. If no row in a group matches, SUM(CASE WHEN ... THEN amount END) returns NULL, not 0. A test with a condition that never matches (status = 'nope') gives NULL for both regions. Wrap it in COALESCE(..., 0) if the report expects a number.
PostgreSQL also supports the standard FILTER clause, which many people find easier to read:
SELECT region,
COUNT(*) FILTER (WHERE status = 'paid') AS paid,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders
GROUP BY region;It produces eu: 2, 0 and us: 1, 1. If the interviewer asks which to use, a fair answer is: FILTER in PostgreSQL code for clarity, CASE inside the aggregate when the query has to run on engines that lack FILTER (MySQL, for one).
Custom sort orders and other places CASE fits
Business rules rarely sort alphabetically. To show pending orders first, then paid, then everything else:
SELECT id, status
FROM orders
ORDER BY CASE status
WHEN 'pending' THEN 0
WHEN 'paid' THEN 1
ELSE 2
END,
id;That returns 4 (pending), then 1, 2, 6 (paid), then 3 and 5. The ELSE 2 also catches the NULL status, which is another reason to always write the ELSE: without it, row 5’s sort key would be NULL, and PostgreSQL sorts NULL last in ascending order by default, which happens to look right until someone adds DESC.
If you find the same CASE copied into five queries, that’s a sign the mapping belongs in data: a small lookup table joined in, or a column. A CASE with forty branches is a table that hasn’t been created yet.
Type rules and evaluation surprises
Every branch must resolve to one common type. CASE WHEN x THEN 1 ELSE 'none'::text END fails in PostgreSQL with CASE types integer and text cannot be matched. Pick a type and cast deliberately, for example return '1' as text or NULL instead of a sentinel string.
PostgreSQL normally evaluates only the branch it needs, so CASE WHEN qty > 0 THEN total / qty ELSE 0 END is the standard way to avoid dividing by zero for row values. The documentation does call out one exception: constant subexpressions can be simplified at planning time, so a literal 1/0 sitting in a branch that never runs may still raise division by zero. Real column expressions are safe; just don’t rely on CASE to hide a broken constant. SQLite, by contrast, returns NULL for division by zero instead of raising an error, so don’t use it to test that particular behaviour.
Interview question: the label that never appears
A colleague’s report shows NULL in a priority_label column for some tickets, and they say it’s impossible because the query handles missing priorities:
SELECT id,
CASE priority
WHEN 1 THEN 'urgent'
WHEN 2 THEN 'normal'
WHEN NULL THEN 'unset'
END AS priority_label
FROM tickets;What are the possible causes of NULL, and how would you rewrite it?
Answer. There are two. First, tickets where priority is NULL: the simple form tests priority = NULL, which is never true, so the 'unset' branch is dead code. Second, tickets with any priority other than 1 or 2 (a 3 someone added last quarter, say) match nothing, and without ELSE they also become NULL. You can’t tell the two apart in the output, so first run SELECT priority, count(*) FROM tickets GROUP BY priority to see which values exist. Then rewrite with the searched form and an explicit fallback:
CASE
WHEN priority IS NULL THEN 'unset'
WHEN priority = 1 THEN 'urgent'
WHEN priority = 2 THEN 'normal'
ELSE 'other (' || priority || ')'
ENDThe ELSE that prints the raw value is a nice touch: it makes the next unexpected priority visible in the report instead of silently blank. A strong answer also mentions that the same three-valued logic explains why WHERE priority = NULL returns no rows.
Keep going
- SQL interview questions and the SQL multiple-choice quiz
- NULL and three-valued logic in SQL
- Aggregation grain, for keeping conditional sums at the right level
- PostgreSQL documentation: conditional expressions