SQL / SORTING, LIMITING, AND BRANCHING OUTPUT
Branching logic with CASE expressions
Write CASE expressions that pick a value per row, control which branch wins when conditions overlap, and decide what unmatched rows return.
What you will learn
- WHEN branches are tested in order; the first TRUE one wins and the rest are skipped
- Add an explicit ELSE or unmatched rows come back as NULL, not as a default label
- Order branches most specific first so a wide condition cannot shadow a narrow one
- Count matches with SUM(CASE WHEN cond THEN 1 ELSE 0 END) in a single query
Understanding Branching logic with CASE expressions
CASE is an expression, not a statement: it is evaluated once per row and collapses to exactly one value, which is why it can sit anywhere a column reference or a literal can, including the select list, inside a function call, inside an aggregate, in WHERE, and in ORDER BY. The shape is CASE, one or more WHEN condition THEN value pairs, an optional ELSE, then END. END is mandatory because the THEN values are themselves arbitrary expressions, so the parser has no other way to tell where the branch list stops and the next select-list item begins.
Branches are tested in the order you wrote them, and the first condition that evaluates to TRUE supplies the result; the remaining WHEN clauses are never evaluated. That is why overlapping ranges have to be ordered narrowest first: a score of 91 also satisfies score >= 50, so whichever of those two tests appears earlier decides the label. That same short-circuiting is what makes CASE WHEN divisor = 0 THEN NULL ELSE amount / divisor END safe, because the division only runs for rows that actually reach that branch.
SQL comparisons return three outcomes, and only TRUE selects a branch. A WHEN whose condition comes out NULL, usually because the compared column is NULL, is skipped exactly like FALSE, so NULL rows slide past every comparison and land in ELSE. When you omit ELSE, SQL supplies ELSE NULL, so those rows produce NULL instead of an error, and because most clients render NULL as a blank cell the omission tends to surface later as a filter or join that quietly loses rows.
CREATE TABLE results (student TEXT, score INTEGER);
INSERT INTO results VALUES
('ana', 91),
('ben', 74),
('cleo', 58),
('dev', 39);
SELECT student,
score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 70 THEN 'B'
WHEN score >= 50 THEN 'C'
ELSE 'F'
END AS grade
FROM results
ORDER BY score DESC;A CASE expression evaluates its WHEN conditions in written order and returns the value of the first one that is TRUE, falling back to ELSE, or to NULL when no ELSE is given.
Worked examples
No ELSE means NULL
Shows that rows matching no WHEN branch get NULL rather than a fallback label.
SELECT student,
score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 70 THEN 'B'
END AS grade
FROM results
ORDER BY score DESC;Example explained
Line 1With no ELSE written, the expression behaves as CASE ... ELSE NULL END.
Line 2cleo and dev satisfy neither condition, so grade is NULL, not an empty string and not 'F'.
Line 3NULL is spelled out here for clarity; psql and the sqlite3 shell print an empty cell instead, which is why the gap is easy to miss.
Line 4Those NULLs also break later filters: WHERE grade <> 'A' would exclude both rows, because NULL <> 'A' is unknown.
Branch order decides the result
The same three thresholds in the wrong order make two branches unreachable.
SELECT student,
score,
CASE
WHEN score >= 50 THEN 'C'
WHEN score >= 70 THEN 'B'
WHEN score >= 90 THEN 'A'
ELSE 'F'
END AS grade
FROM results
ORDER BY score DESC;Example explained
Line 1WHEN score >= 50 THEN 'C' is tested first, and 91 satisfies it, so ana is graded C.
Line 2Because a TRUE branch stops the search, the >= 70 and >= 90 clauses can never run for any row.
Line 3Only dev fails every condition, so F is the one other label that still appears.
Line 4Nothing here is a syntax error, and no engine warns you that two branches are dead code.
Counting with CASE inside an aggregate
Turns conditions into 1/0 values so several conditional counts fit in one result row.
INSERT INTO results VALUES ('eve', NULL);
SELECT COUNT(*) AS students,
SUM(CASE WHEN score >= 70 THEN 1 ELSE 0 END) AS passed,
SUM(CASE WHEN score IS NULL THEN 1 ELSE 0 END) AS not_sat
FROM results;Example explained
Line 1Each CASE reduces a condition to 1 or 0 per row, so SUM over those numbers counts the matching rows.
Line 2eve has no score, so score >= 70 is unknown, that branch is skipped, and ELSE 0 keeps her out of passed.
Line 3The IS NULL branch is the only way to count her; WHEN score = NULL would never be TRUE.
Line 4COUNT(*) still sees all five rows, so the three numbers describe the same population and can be compared.
Important notes
Every THEN and ELSE must collapse to one data type, since the result is a single column: Postgres rejects THEN 0 ... ELSE 'none' outright, while SQLite and MySQL coerce it quietly, so mixed-type branches behave differently per engine.
END is required, and there is no ELSEIF keyword in a CASE expression: additional branches are simply additional WHEN clauses, evaluated in the order you typed them.
Common mistakes
Omitting ELSE and then filtering on the result: unmatched rows are NULL, so a predicate like WHERE grade <> 'A' silently discards them instead of keeping them.
Writing the widest range first, as in WHEN score >= 50 before WHEN score >= 90: every row above 50 gets the first label and the stricter branches become unreachable.
Testing for missing data with WHEN score = NULL: the comparison is unknown rather than TRUE, so those rows drop into ELSE; the branch has to be WHEN score IS NULL.
Try it yourself
Change, predict, then run
Recreate the results table, add a fifth row ('eve', NULL), and write one query that labels every student 'pass' for 70 or above, 'fail' below 70, and 'no score' when score is NULL, so that no row shows a blank label cell.
Open the SQL workspaceCheck your understanding
A CASE expression lists WHEN score >= 50 THEN 'pass' first and WHEN score >= 80 THEN 'distinction' second, with no ELSE. For the scores 90, 60 and NULL, what does it return?
- 'distinction', 'pass', and NULL
- 'pass', 'pass', and NULL
- 'distinction', 'pass', and 'pass'
- 'pass', 'pass', and an error, because no ELSE branch exists
Show answer
90 makes the first condition TRUE, so the search stops there and 'distinction' is unreachable; for NULL both comparisons are unknown rather than TRUE, so the implicit ELSE NULL applies. The first option assumes the engine picks the best-fitting branch, but branches are not ranked in any way, only ordered, and a missing ELSE is perfectly legal, so nothing raises an error.