CASE WHEN Expressions in SQL
The CASE WHEN expression evaluates a series of conditions and returns a value for the first condition that is true. It can appear anywhere in a query where a value expression is valid: in SELECT, WHERE, ORDER BY, and inside aggregate functions.
CASE WHEN evaluates a list of conditions in order and returns the result from the first one that matches.
You're building a product report and the catalog team wants a pricing tier column on every row. Products above $500 should be labeled 'premium,' everything else 'standard.' Instead of exporting to a spreadsheet and adding the column manually, you compute it directly in the query. Any time you need to attach a classification, a status label, or a conditional value to each row, CASE WHEN is how you do it.
The expression checks each WHEN branch against the current row, top to bottom, and returns the THEN value for the first condition that's true. Once it finds a match, it stops. The rest of the branches don't run.
Here's the pricing tier example:
SELECT name, price,
CASE WHEN price > 500 THEN 'premium'
ELSE 'standard'
END AS price_tier
FROM productsEvery row gets a price_tier value. ELSE is the fallback for any row where no WHEN condition matched.
You can stack as many WHEN branches as you need. The ordering matters: SQL returns on the first match and stops. Write the most specific conditions first, the most general last:
SELECT id, total_amount,
CASE WHEN total_amount > 1500 THEN 'premium'
WHEN total_amount > 500 THEN 'high'
ELSE 'standard'
END AS tier
FROM ordersA $1,600 order hits the first branch and gets 'premium.' A $700 order passes the first branch and gets 'high.' Anything under $500 falls through to ELSE. Swap the first two branches and a $1,600 order would return 'high' instead — the order of WHEN branches is the order of evaluation.
CASE WHEN can also compute values, not just labels:
SELECT id, total_amount,
CASE WHEN total_amount > 800 THEN total_amount * 0.9
ELSE total_amount
END AS adjusted_total
FROM ordersHigh-value orders get 10% knocked off; everything else is unchanged.
Handling NULL requires an explicit branch. A WHEN condition that compares against NULL using = produces NULL, which SQL treats as false. The condition fails silently and the row falls to the next branch. If NULL in a column should map to a specific label, write an IS NULL branch and place it first:
The IS NULL branch comes first because any row with NULL would silently pass through the numeric comparison branches without matching. Placing it first catches NULL before any other condition runs.
The one thing that trips people up: omitting ELSE.
If no condition matches and there's no ELSE, CASE returns NULL. Not an empty string. Not an error. NULL. That NULL flows silently into the output and produces unexpected results wherever the column is used. Writing an explicit ELSE makes the fallback behavior visible in the code instead of implied by omission — even if the fallback is just 'other' or 'unknown.'
10 CASE WHEN Expressions practice problems
- `'premium'` if the price is above `$500`. - `'standard'` for all other prices.
Write a query to return each order's ID, status, and urgency label.
- `'executive'` for employees who have no manager on record (i.e., `manager_id` is `NULL`). - `'staff'` for everyone else.
Write a query to return each product's name, stock quantity, and `stock_status` label.
- `'high value'` for orders above `$1,000`. - `'standard'` for all other delivered orders.
- `'high value domestic'` if the customer is US-based **and** has a city on record. - `'other'` for everyone else (non-US, or US with no city, or any other combination).
- For orders with `total_amount > $800`, the adjusted total is the original `total_amount` scaled by `0.9` (a 10% reduction). - For all other orders, the adjusted total equals the original `total_amount` (unchanged).
- `'uncategorized'` for products with no category assigned (`category_id` is `NULL`). - `'specialty'` for products with `category_id >= 5`. - `'general'` for all other products.
- `'premium'` for orders with `total_amount > $1,500`. - `'high'` for orders with `total_amount > $500` but **not above** `$1,500`. - `'standard'` for all other orders.
Write a query to return every user's ID, plan, and tag.
These problems are part of the CASE WHEN Expressions lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.