Loading...
CASE WHEN is just if/else for SQL: it walks your conditions top to bottom and returns the first one that is true. Flip the conditions below and watch every row re-bucket.
Conor Robertson
July 8, 2026 · 10 min read
The questions that come up most once people start writing CASE expressions for real.
QueryCase teaches SQL through mystery cases on real datasets. Music, sports, film, and more. Your first cases are completely free.
Start your first caseFree to start. No credit card required.
CASE is the first taste most people get of real logic inside SQL, and it is far friendlier than it looks. If you can read an if/else in any other language, you already understand it. It checks conditions one by one, top to bottom, and hands back the value attached to the first one that is true.
Above is a leaderboard of five detectives, and a CASE expression sorting them into ranks by the XP they have earned. That is the whole idea: a ladder of conditions that turns a raw number into a readable label. Tap any detective to watch CASE walk the branches and pick one. Then flip the branch order and see four of them collapse into the wrong bucket, because CASE stops at the first match every single time.
Everything below is really just that one behaviour, shown from a few angles: a ladder of conditions, checked in order, first true one wins.
CASE is SQL's if/else. It reads a list of WHEN condition THEN value branches, checks them in order, and returns the value from the first branch whose condition is true. If none are true, it falls back to ELSE, or to NULL if there is no ELSE. The whole thing resolves to one value per row, so it slots in anywhere a column would: in SELECT, WHERE, ORDER BY, even inside a SUM or COUNT.
In one line: CASE WHEN xp >= 3500 THEN 'Elite' ELSE 'Recruit' END reads as "if XP is at least 3,500 call them Elite, otherwise Recruit." Stack more WHEN branches and you get more bands. That is really all there is to it.
The mental model
Picture a ladder of conditions. A row starts at the top and falls down it, testing each rung. The moment a rung is true, the row grabs that label and stops falling. It never checks the rungs below. That "stops at the first true rung" behaviour is the entire personality of CASE, and it is behind both its best trick (clean bucketing) and its worst bug (conditions in the wrong order).
Here is the same idea as a picture: five detectives on the left, sorted by one CASE expression into three tiers on the right.
Each detective's XP is checked against the tiers, and they land in exactly one bucket. Change the thresholds and the buckets refill. That is bucketing, and it is the single most common thing CASE is used for.
Every CASE reads the same way once you can name its four parts. Learn them on one expression and you can read any of them.
CASE and the first WHEN) it is a searched CASE, which tests a full condition on each branch.WHEN holds a condition and THEN the value to return if it is true. You can stack as many as you like. They are tested top to bottom and the first true one wins, so the rest are never checked.WHEN. It is optional, but leaving it out is not the same as skipping it: with no ELSE, unmatched rows return NULL.CASE ... END resolves to a single value per row, which is why you can drop it into SELECT, WHERE, ORDER BY or an aggregate.The shape never changes: open with CASE, stack one or more WHEN ... THEN ... branches, add an optional ELSE, and close with END. The only two decisions you ever make are what conditions to test and in what order.
There are two ways to write the WHEN branches, and knowing which is which clears up most of the confusion beginners hit. The difference is whether you name a column once up front, or write a full condition on every branch.
The column goes once, right after CASE. Each WHEN is just a value to match. Tidy, but it can only ever test equality.
No column after CASE. Each WHEN carries a full condition, so ranges, AND / OR and IS NULL all work. This is the one to learn.
Same ladder, different reach. Simple is shorthand for equality on a single column. Searched handles any condition, which is why it is the form you will write most.
The searched form on the right is the one to internalise. Because each WHEN carries a complete condition, it can do ranges, AND / OR, IS NULL and comparisons across different columns. The simple form is a neat shortcut, but only when every branch is checking the same column for an exact match. When in doubt, write the searched form.
This is the rule that catches everyone, so it is worth burning in. CASE evaluates from the top and returns the first branch that is true, then stops. It does not find the "best" match or the most specific one. It finds the first one.
Go back to the explorer and switch branch order to loosest first. The condition xp >= 1000 jumps to the top, and because it is true for nearly everyone, it fires before the tighter rules ever run. Four detectives who should be Elite or Senior all crash into Junior. The fix is always the same: order overlapping conditions from most specific to most general.
No ELSE means NULL, not nothing
The other classic trap hides in the missing ELSE. When a row matches no WHEN and there is no ELSE, CASE does not skip the row or return an empty string. It returns NULL. Toggle the explorer's fallback to no ELSE and watch Quill turn to NULL. If you ever see unexpected NULLs in a CASE column, a missing ELSE is the first thing to check.
CASE looks like a one-trick tool for labelling, but it quietly powers a handful of everyday patterns. These are the ones worth recognising on sight.
The second card, conditional aggregation, is the one that turns CASE from a nicety into a power tool, so it deserves its own worked example.
Here is a trick that comes up constantly in real reporting and in SQL interview questions: counting or totalling several categories at once, side by side, in a single query. The move is to put a CASE inside an aggregate. Each CASE returns a value only for the rows that match its condition, and NULL for the rest, which aggregates quietly ignore.
SELECTCOUNT(CASE WHEN status = 'solved' THEN 1 END) AS solved, COUNT(CASE WHEN status = 'open' THEN 1 END) AS open_cases, COUNT(CASE WHEN status = 'cold' THEN 1 END) AS cold FROM cases;
Instead of three separate queries, or a GROUP BY that stacks the results into rows, this hands back one tidy row with a column per status. Swap COUNT for SUM and 1 for a real amount and you get filtered totals: SUM(CASE WHEN region = 'US' THEN sales END) gives US sales in its own column, ready to sit beside EU and APAC. This "rows into columns" reshape is exactly what people mean by pivoting in plain SQL, and CASE is the engine behind it.
Once the idea clicks, the only thing left is matching the job to the shape. Read this one backwards: find the result you are after on the left, and the pattern is on the right.
| You want | The pattern | In the wild |
|---|---|---|
| Turn a number into a labelled band | CASE WHEN x >= n THEN 'label' ... | Scores into A / B / C grades |
| Map known values to new ones | CASE col WHEN 'a' THEN ... END | Status codes into words |
| A yes/no flag beside each row | CASE WHEN cond THEN 1 ELSE 0 END | Flag orders over £100 |
| Spread one column into many | SUM(CASE WHEN cond THEN x END) | Sales per region, side by side |
| Count only matching rows | COUNT(CASE WHEN cond THEN 1 END) | Signups this month vs last |
| Your own sort priority | ORDER BY CASE WHEN ... THEN 0 ... END | 'urgent' tickets pinned to top |
| Reshape NULLs your way | CASE WHEN x IS NULL THEN ... END | Blank emails become 'no contact' |
| A different value per row | just CASE ... END | Tax rate that varies by country |
The good news for interviews and for portable code: CASE is core standard SQL and behaves identically everywhere. The exact same CASE WHEN ... THEN ... ELSE ... END runs unchanged in PostgreSQL, MySQL, SQLite, SQL Server, BigQuery, Snowflake and Redshift, both simple and searched forms, same first-match-wins rule. Learn it once and it travels.
What differs are the shortcuts each dialect adds for the simplest case, a single two-way choice:
IF and IIF. MySQL has IF(condition, a, b) and SQL Server has IIF(condition, a, b). Both are just a two-branch CASE with less typing, and neither is portable. For anything with more than one condition, or for SQL that has to run on more than one database, use CASE.DECODE. Oracle's DECODE(col, val1, res1, val2, res2, default) is an older equality-only shorthand, essentially a simple CASE. Modern Oracle supports standard CASE, so there is no reason to reach for DECODE in new code.COALESCE and NULLIF. These are not rivals to CASE, they are named special cases of it. COALESCE(a, b) is "the first non-null value", and NULLIF(a, b) is "null if these are equal". Both are just tidy CASE expressions with a common job, and they read better than spelling the CASE out by hand.So a CASE you write here works in a BigQuery, Snowflake or SQL Server interview without a single change. Only the optional shorthands move.
A detective has 3,950 XP. What does this CASE return for them?
A single CASE resolves to one value, so every THEN and the ELSE have to return a compatible type. Mixing a number in one branch and text in another will either be silently coerced or throw an error, depending on the database. If you need a number to display alongside text labels, cast it explicitly rather than leaning on the database to guess.
If you write CASE ... END AS tier, you cannot then refer to tier in another expression in the same SELECT, or in the WHERE, because the alias does not exist yet while the row is being built. Repeat the whole CASE, or wrap the query in a CTE or subquery and refer to the alias in the outer query, the same wrap-then-use pattern that window functions need.
CASE status WHEN NULL THEN ... never matches, because NULL = NULL is not true in SQL, it is unknown. To catch nulls you need the searched form with IS NULL: CASE WHEN status IS NULL THEN 'missing' .... This is a subtle one, and it is another reason to default to searched CASE.
The fastest way to learn this is to write it, not read it. Reading about CASE gets you to "I follow that." Writing a bucketing expression, watching the labels land, then deliberately breaking the order to see everything collapse, gets you to "I reach for CASE without thinking." The gap is just reps: pick a column, band it, and check the labels against what you expected.
Once CASE feels automatic, it slots in beside the other building blocks: JOIN combines tables and window functions rank the rows within them. Same detective data, three tools that work together.