Every query so far returned values exactly as they sat in the table. But real reports need derived labels: bucket salaries into bands, turn a status code into readable text, flag rows that meet a condition. CASE is SQL's if/then/else: it computes a new value, row by row, based on conditions you write.
CASE WHEN condition THEN value ... ELSE fallback END evaluates conditions top to bottom and returns the value for the first one that's true. If none match, you get the ELSE value, or NULL if there's no ELSE.
A new column from a condition
CASE produces a value, so it lives right in the SELECT list like any other column. It doesn't drop or combine rows; it adds a derived column, giving each row the label of the first WHEN that matches:
| name | salary |
|---|---|
| Ada | 48000 |
| Bo | 62000 |
| Cy | 71000 |
| Di | 55000 |
| Eli | 39000 |
+ CASE … END AS band| name | salary | band |
|---|---|---|
| Ada | 48000 | junior |
| Bo | 62000 | mid |
| Cy | 71000 | senior |
| Di | 55000 | mid |
| Eli | 39000 | junior |
Try it now: write the CASE the visual showed, add a band column (senior ≥ 70000, mid ≥ 50000, else junior).
Your turn · Exercise
Add a band column: senior for salary >= 70000, mid for >= 50000, otherwise junior. Return name, salary, band.
The data
employees
5 rows returned
| name | salary |
|---|---|
| Ada | 48,000 |
| Bo | 62,000 |
| Cy | 71,000 |
| Di | 55,000 |
| Eli | 39,000 |
Run it: label each employee by salary band.
The order matters: conditions are checked top to bottom and the first true one wins. Cy earns 71000, which is both >= 70000 and >= 50000, but because senior comes first, that's the label Cy gets.
Watch out
Order your WHEN branches from most specific to most general. If you flip them and put WHEN salary >= 50000 THEN 'mid' first, then everyone earning 70000+ also passes the 50000 test, gets labelled mid, and the senior branch is dead code that never fires.
No ELSE means NULL
The ELSE is optional. Leave it out and any row matching no WHEN returns NULL for that column. Easy to forget, easy to misread. Run it and watch the unmatched rows come back empty:
pending and cancelled match no branch, so revenue_effect is NULL for them. Add ELSE 'no effect' to give them a label.
CASE inside an aggregate: conditional counting
The trick that makes CASE indispensable: put it inside an aggregate to count or sum only the rows that meet a condition: many summaries in one query, no separate filters needed. Run it:
This pattern, SUM(CASE WHEN ... THEN 1 ELSE 0 END), is how you count rows matching a condition without writing a separate query for each. Swap the 1 for a column to sum that column conditionally instead.
From practice
Reach for CASE whenever a report needs a label that isn't stored in the table (bands, buckets, human-readable statuses, "yes/no" flags) or when you want several conditional counts in a single pass. Conditional aggregation (SUM(CASE WHEN ...)) is one of the most reused patterns in all of analytics SQL.
Your turn
Now the pattern that makes CASE indispensable: conditional aggregation. In a single row, count the paid and refunded orders using SUM(CASE WHEN … THEN 1 ELSE 0 END).
Your turn · Exercise
In one row, return paid_orders (count of paid orders) and refunded_orders (count of refunded orders), using SUM(CASE WHEN ... THEN 1 ELSE 0 END).
The data
orders
5 rows returned
| id | status |
|---|---|
| 1 | paid |
| 2 | refunded |
| 3 | paid |
| 4 | pending |
| 5 | paid |
Next: the last beginner building block, putting one query inside another with subqueries.