SQL / SORTING, LIMITING, AND BRANCHING OUTPUT
Searched versus simple CASE and choosing between them
Choose between CASE x WHEN v and CASE WHEN cond deliberately, and know why the simple form can never match a NULL operand.
What you will learn
- Read CASE x WHEN v as shorthand for the searched form CASE WHEN x = v.
- Reach for the searched form for ranges, inequalities, IS NULL, or multi-column tests.
- Catch NULL with WHEN x IS NULL; a simple CASE WHEN NULL branch never fires.
- Never put a comparison after CASE x WHEN: it tests x against a boolean 1 or 0.
Understanding Searched versus simple CASE and choosing between them
CASE comes in two shapes that look similar and behave differently. In the simple form you name one operand right after CASE, as in CASE status WHEN 'paid', and the engine turns each WHEN into an implicit equality test against that operand. In the searched form there is no operand; each WHEN carries a complete predicate that stands on its own, so different branches can test different columns, ranges, or patterns. The standard defines the simple form as shorthand for the searched one: CASE x WHEN a THEN r END means CASE WHEN x = a THEN r END, so the simple form can never express anything the searched form cannot.
That hidden equality is an ordinary SQL comparison, and this is where the simple form bites. Because x = NULL evaluates to unknown rather than true, a branch written WHEN NULL matches nothing, and a row whose operand is NULL falls through to ELSE, or to NULL when you left ELSE out. The same rule means the match inherits type coercion and collation, so whether 'paid' matches 'PAID' is decided by the column's collation, not by CASE. Only the searched form can ask IS NULL, so any branch that must recognize missing data forces that shape on the whole expression.
Choosing is therefore about what the branches test, not about style. When every branch maps one expression to one constant, such as a status code or a string length, the simple form states that plainly and writes the operand once instead of repeating it in every WHEN, which matters when the operand is something like substr(sku, 1, 3). The moment one branch needs >=, BETWEEN, LIKE, IS NULL, or a second column, convert the entire expression to the searched form rather than smuggling a condition into a simple CASE. Rewriting simple to searched is mechanical, so starting simple costs you nothing later.
WITH orders(id, status) AS (
SELECT 1, 'paid' UNION ALL
SELECT 2, 'refunded' UNION ALL
SELECT 3, 'pending' UNION ALL
SELECT 4, NULL
)
SELECT
id,
status,
CASE status
WHEN 'paid' THEN 'closed'
WHEN 'refunded' THEN 'closed'
WHEN 'pending' THEN 'open'
WHEN NULL THEN 'missing'
END AS simple_form,
CASE
WHEN status IS NULL THEN 'missing'
WHEN status IN ('paid', 'refunded') THEN 'closed'
WHEN status = 'pending' THEN 'open'
END AS searched_form
FROM orders
ORDER BY id;A simple CASE is only shorthand for a searched CASE whose every branch is operand = value, which is exactly why it cannot express ranges or match NULL.
Worked examples
Ranges only fit the searched form
Bucketing a numeric score shows why branch order matters and why the simple form cannot do this at all.
WITH t(name, score) AS (
SELECT 'ana', 91 UNION ALL
SELECT 'ben', 74 UNION ALL
SELECT 'cleo', 58
)
SELECT name, score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 70 THEN 'B'
ELSE 'C'
END AS grade
FROM t
ORDER BY score DESC;Example explained
Line 1No operand follows CASE, so each WHEN is read as a standalone true/false predicate.
Line 291 satisfies both score >= 90 and score >= 70, and the first match wins, so branch order defines the buckets.
Line 3Swapping the two branches would label every score above 70 as B and never reach A.
Line 4A simple CASE cannot do this: it would compare score for equality with the constants 90 and 70.
A condition after the operand misfires
Putting a comparison in the WHEN of a simple CASE compares the operand with a boolean result, shown here in SQLite and MySQL.
WITH t(id, score) AS (
SELECT 1, 80 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 0
)
SELECT id, score,
CASE score WHEN score > 50 THEN 'high' ELSE 'low' END AS broken,
CASE WHEN score > 50 THEN 'high' ELSE 'low' END AS fixed
FROM t
ORDER BY id;Example explained
Line 1CASE score WHEN score > 50 compares score with the value of score > 50, never with 50.
Line 2SQLite and MySQL evaluate that comparison to 1 or 0, so row 3 matches on 0 = 0 and a zero score is labeled high.
Line 3Row 1 fails because 80 = 1 is false, so the ELSE branch fires for the highest score.
Line 4Deleting the operand gives the fixed column; Postgres and SQL Server refuse the broken version with an integer-versus-boolean type error.
One operand, several constants
The two forms return identical results when every branch is an equality test, and the simple form writes the operand once.
WITH parts(code) AS (
SELECT 'AB1' UNION ALL
SELECT 'XY42' UNION ALL
SELECT 'Q'
)
SELECT code,
CASE length(code)
WHEN 1 THEN 'short'
WHEN 3 THEN 'std'
WHEN 4 THEN 'long'
END AS simple_form,
CASE
WHEN length(code) = 1 THEN 'short'
WHEN length(code) = 3 THEN 'std'
WHEN length(code) = 4 THEN 'long'
END AS searched_form
FROM parts
ORDER BY code;Example explained
Line 1length(code) appears once as the operand, while the searched version repeats it in all three branches.
Line 2Both columns agree on every row because the simple form is defined as exactly that equality rewrite.
Line 3A two-character code would match no branch, and with no ELSE the result would be NULL.
Line 4If one bucket later became length(code) > 4, only the searched column could hold it and the simple form would have to be converted.
Important notes
The CASE statement inside stored procedures and PL/pgSQL offers the same two shapes but ends with END CASE and holds statements; the expression form here always yields a single value.
You can keep the simple form and still catch NULL with CASE COALESCE(status, 'none') WHEN 'none' ..., but that then treats a genuine 'none' value as missing.
Common mistakes
Writing WHEN NULL THEN 'unknown' in a simple CASE: NULL = NULL is unknown, so the branch never fires and NULL rows land in ELSE, leaving the unknown bucket empty.
Putting a condition after the operand, as in CASE score WHEN score >= 60 THEN 'pass': Postgres raises a type error, while MySQL and SQLite compare score with 1 or 0 and mark nearly every row 'fail'.
Grouping values with CASE x WHEN 1 OR 2 THEN 'low': this compares x with the value of 1 OR 2, which is 1 in MySQL and SQLite, so only x = 1 matches; repeat the WHEN or move to a searched WHEN x IN (1, 2).
Try it yourself
Change, predict, then run
In a browser SQL editor, build a five-row CTE of shipments with carrier and weight_kg, leaving one weight NULL, then write one SELECT that labels carrier with a simple CASE and buckets weight into light, medium, heavy and unknown with a searched CASE. Confirm the NULL row is labeled unknown only by the searched column.
Open the SQL workspaceCheck your understanding
In SQLite and MySQL the expression CASE flag WHEN flag > 0 THEN 'yes' ELSE 'no' END runs without error. Which rows come back as 'yes'?
- Every row where flag is greater than 0, the same as the searched form
- Only rows where flag is 0 or 1
- No rows, because the WHEN clause is not a valid comparison
- Only rows where flag is 1, since a true comparison is 1
Show answer
The operand flag is compared with the value of flag > 0, which is 1 or 0 in these engines, so a match needs flag to equal that result: flag = 1 matches through the true branch and flag = 0 matches because false is 0. The first option is the trap, since it assumes the WHEN holds a condition, which is only true when there is no operand after CASE; the last option is half right but forgets that a false comparison yields 0, which a zero flag also equals.