CASE computes a value from conditions inside a query, which keeps classification logic next to the data instead of in application code. It comes in two forms: a simple form that compares one expression to a list, and a searched form that evaluates independent boolean conditions in order.
Before you start
You should be comfortable with SELECT, WHERE and basic expressions. This article uses PostgreSQL syntax; the structure is standard across engines.
Step-by-step walkthrough
Step 1: Choose simple or searched form
The simple form CASE status WHEN 'a' THEN 1 ... END compares one expression to values. The searched form CASE WHEN amount >= 100 THEN ... END evaluates full conditions, which is what you need for ranges. Use the searched form whenever the condition is not a plain equality.
Step 2: Always decide the ELSE case
Without ELSE, a value that matches no branch is NULL. If that value flows into a calculation or a comparison, the NULL propagates and can silently zero out sums or fail a filter. Add an explicit ELSE unless NULL is genuinely intended.
Step 3: Use CASE in ORDER BY and aggregation
A CASE in ORDER BY creates a custom sort key, such as put priority items first. Wrapping it in SUM or COUNT with a condition gives conditional aggregation, which pivots counts into columns in one pass instead of several queries.
Worked scenario
The searched form classifies each order into a band.
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;Walk through the example
Branches are evaluated top to bottom, so the first matching condition wins and a 1500 order lands in high without also matching medium. The explicit ELSE 'low' guarantees a value for every row, so the column has no NULL. The result is a derived label computed at query time.
Common mistake
Omitting ELSE and then comparing the result to a value, where NULL makes every comparison unknown and the row disappears. Another is using CASE on a nullable expression without handling NULL, since CASE x WHEN NULL never matches, and you need WHEN x IS NULL.
Verify the behavior
Test boundary values 100 and 1000 and confirm they fall in the intended band. Add a row that matches no branch and confirm the ELSE value appears rather than NULL. Compare a conditional SUM(CASE ...) with separate filtered counts to confirm they agree.
Interview exercise
Why does CASE status WHEN NULL THEN 'unknown' END never return 'unknown'?
Answer and reasoning
The simple form uses equality, and status = NULL is unknown rather than true, so the branch never matches. Use the searched form with CASE WHEN status IS NULL THEN 'unknown' ELSE status END. This is the same three-valued-logic rule that makes = NULL fail in a WHERE clause.
Continue learning
Compare NULL behavior in SQL NULL three-valued logic and aggregation grain. Read the PostgreSQL conditional expressions documentation and try the SQL interview questions.