CASE expressions
SQL's if/else — conditional logic inside SELECT, ORDER BY, and aggregates. The Swiss-army knife you reach for constantly.
What you'll learn
- Searched CASE versus simple CASE, and when to use each
- Using CASE inside SELECT, ORDER BY, and aggregates
- The conditional-sum trick that collapses five queries into one
Before you start
CASE is SQL’s if/else. Anywhere a column or expression can go — inside SELECT, WHERE, ORDER BY, even inside an aggregate — you can drop a CASE, which is what makes it one of the most versatile tools in the language.
It comes in two forms. Searched CASE takes full boolean conditions, top to bottom, and the first match wins:
CASE
WHEN price < 5 THEN 'cheap'
WHEN price < 20 THEN 'mid'
ELSE 'premium'
ENDSimple CASE compares one value against a list (CASE category WHEN 'Stationery' THEN … END). Either way, if no branch matches and there is no ELSE, the result is NULL — so always think about the unmatched rows. The “first match wins, top to bottom” rule is the key thing to picture:
Each WHEN is checked top to bottom — the first match wins and the rest are skipped.
CASE in SELECT — a derived column
Bucket each product into a price tier:
SELECT name, price,
CASE
WHEN price < 5 THEN 'cheap'
WHEN price < 20 THEN 'mid'
ELSE 'premium'
END AS tier
FROM products
ORDER BY price;| name | price | tier |
|---|---|---|
| Pencil | 2 | cheap |
| Notebook | 6 | mid |
| Coffee | 9 | mid |
| Cable | 12 | mid |
| Laptop | 900 | premium |
The same CASE works inside ORDER BY when you need a custom sort that is not alphabetical — ORDER BY CASE category WHEN 'Electronics' THEN 1 WHEN 'Grocery' THEN 2 ELSE 3 END puts your business priority first.
CASE inside an aggregate — the conditional sum
Here is the move that replaces five queries with one. Put a CASE inside SUM and you can count several different conditions side by side in a single pass. Take a small orders table:
| order_id | country | qty |
|---|---|---|
| 1 | IN | 1 |
| 2 | IN | 3 |
| 3 | US | 2 |
| 4 | US | 5 |
SELECT country,
COUNT(*) AS total_orders,
SUM(CASE WHEN qty = 1 THEN 1 ELSE 0 END) AS single_item,
SUM(CASE WHEN qty >= 3 THEN 1 ELSE 0 END) AS bulk_orders,
SUM(CASE WHEN qty >= 3 THEN qty ELSE 0 END) AS bulk_units
FROM orders
GROUP BY country;| country | total_orders | single_item | bulk_orders | bulk_units |
|---|---|---|---|---|
| IN | 2 | 1 | 1 | 3 |
| US | 2 | 0 | 1 | 5 |
Read each SUM(CASE …) as a sentence: “add 1 every time the condition holds, otherwise 0” — that is just counting matching rows. Swap the 1 for a value (like qty) and you get a conditional total instead. The ELSE 0 is not optional: drop it and non-matching rows produce NULL, which SUM silently skips — giving a lower total than you meant.
Practice
Quick check
Practice this in an interview
All questionsWrap a CASE WHEN expression inside an aggregate function — typically SUM or COUNT — to selectively accumulate values for a specific category while leaving all other rows contributing zero or NULL. This produces one column per category from a single GROUP BY pass.
SQL processes clauses in this order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. This matters because it explains why you cannot use a SELECT alias in a WHERE clause, but you can use it in ORDER BY.
A column alias defined in SELECT can be referenced in ORDER BY but not in WHERE or HAVING, because SELECT runs after those clauses in the logical processing order. To reuse a complex expression in WHERE or HAVING, repeat the expression or wrap the query in a subquery or CTE.
Every core SQL clause — SELECT, WHERE, GROUP BY, HAVING, JOIN, ORDER BY, LIMIT — has a direct pandas equivalent, but SQL executes inside a database engine with optimized query planning and disk-backed storage, while pandas requires all data to fit in RAM. Use SQL for large persistent datasets and pandas for in-memory transformation, feature engineering, and integration with the Python ML ecosystem.