05 · CASE Expressions¶
CASE is SQL's inline if/else — it evaluates conditions and returns a value,
right inside a SELECT, WHERE, or ORDER BY clause. It's how you turn raw
data into human-readable labels, bucket values into ranges, or build
pivot-style summaries without leaving SQL.
Sample schema¶
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
amount REAL NOT NULL,
status_code INTEGER NOT NULL
);
INSERT INTO orders (amount, status_code) VALUES
(15.00, 1),
(85.00, 2),
(250.00, 3),
(40.00, 1),
(600.00, 2);
Searched CASE — arbitrary conditions¶
The most common form checks a different condition per branch, evaluated top to bottom, stopping at the first match:
SELECT id, amount,
CASE
WHEN amount < 50 THEN 'small'
WHEN amount < 200 THEN 'medium'
ELSE 'large'
END AS order_tier
FROM orders;
id amount order_tier
-- ------ ----------
1 15.0 small
2 85.0 medium
3 250.0 large
4 40.0 small
5 600.0 large
Order matters: WHEN amount < 200 only gets checked for rows that already
failed amount < 50, so it effectively means "between 50 and 200" without
needing to write that range explicitly. Reversing the branch order would
change the result — always put the narrowest or most specific condition
first.
Simple CASE — comparing one expression against several values¶
When every branch compares the same column against a specific value, the simple form is more compact:
SELECT id, status_code,
CASE status_code
WHEN 1 THEN 'pending'
WHEN 2 THEN 'shipped'
WHEN 3 THEN 'delivered'
ELSE 'unknown'
END AS status_label
FROM orders;
id status_code status_label
-- ----------- -------------
1 1 pending
2 2 shipped
3 3 delivered
4 1 pending
5 2 shipped
This is equivalent to CASE WHEN status_code = 1 THEN ... WHEN status_code =
2 THEN ..., just shorter when it's always the same column being tested.
Missing ELSE means NULL¶
SELECT id, status_code,
CASE status_code
WHEN 1 THEN 'pending'
WHEN 2 THEN 'shipped'
END AS status_label
FROM orders;
id status_code status_label
-- ----------- -------------
1 1 pending
2 2 shipped
3 3 NULL
4 1 pending
5 2 shipped
Order 3 has status_code = 3, which matches no WHEN branch, and there's no
ELSE — so the whole expression evaluates to NULL rather than raising an
error. This is easy to miss in testing if your sample data happens to cover
every case; always add an explicit ELSE (even ELSE 'unknown') so unmapped
values are visibly flagged instead of silently becoming NULL.
Conditional aggregation — pivoting with SUM(CASE ...)¶
Combining CASE with an aggregate function is the standard way to build a
pivot-style summary — one row, one column per category — without a dedicated
PIVOT clause (which SQLite, and standard SQL generally, doesn't have):
SELECT
SUM(CASE WHEN amount < 50 THEN 1 ELSE 0 END) AS small_count,
SUM(CASE WHEN amount >= 50 AND amount < 200 THEN 1 ELSE 0 END) AS medium_count,
SUM(CASE WHEN amount >= 200 THEN 1 ELSE 0 END) AS large_count
FROM orders;
Each CASE contributes 1 to its SUM when the condition matches and 0
otherwise — so each SUM(CASE ...) becomes a conditional counter. This
pattern (sometimes written SUM(CASE WHEN cond THEN amount ELSE 0 END) to
total a value instead of counting rows) is one of the most useful tricks in
reporting SQL: it turns several separate filtered queries into a single pass
over the data.
CASE in ORDER BY — custom sort order¶
Here, delivered orders (status_code = 3) are pulled to the front regardless
of their id, because the CASE maps them to sort key 0 and everything
else to 1; the trailing , id breaks ties within each group. This is the
standard way to express a sort order that doesn't match any column's natural
alphabetical or numeric ordering — like "pending, then shipped, then
delivered" instead of alphabetical.
Cheat sheet¶
| Form | Syntax | When to use |
|---|---|---|
Searched CASE |
CASE WHEN cond1 THEN a WHEN cond2 THEN b ELSE c END |
Different conditions per branch, ranges |
Simple CASE |
CASE col WHEN v1 THEN a WHEN v2 THEN b END |
Same column compared to several exact values |
No ELSE |
— | Unmatched rows become NULL — always add ELSE deliberately |
SUM(CASE WHEN cond THEN 1 ELSE 0 END) |
— | Conditional counting / pivot-style summaries |
CASE in ORDER BY |
— | Custom sort order that isn't alphabetical/numeric |
How It Actually Works¶
CASE WHEN ... THEN ... END compiles into a straight sequence of conditional
jump opcodes in the VDBE — functionally it's an if/elif/else chain evaluated
top to bottom for every row, stopping at the first matching WHEN (later
branches are never evaluated once one matches, so order conditions from most
to least selective if they're expensive to check). Because it's evaluated
per-row inline during the scan, a CASE expression itself doesn't prevent
index use on other filter columns in the same query — but like any
function, wrapping the indexed column itself inside a CASE in the WHERE
clause blocks that column's index from being used, for the same reason
calling a function on a column does. When used in ORDER BY or GROUP BY
as a bucketing trick (CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END),
the engine has no choice but to materialize the computed value for every row
into the temporary sort/group B-tree, since there's no way to seek an index
by a value that doesn't physically exist in any column.
Exercise¶
Using the orders table above:
- Write a searched
CASEthat labels orders as'refund risk'whenamount > 500,'watch'whenamount > 100, and'normal'otherwise. - Write a query using conditional aggregation to count how many orders fall
into each
status_code, all in a single row (one column per status). - Write a query that sorts orders so
status_code = 1(pending) always comes last, everything else in its normal numeric order.