CASE WHEN Expressions in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Literal Values, Data Types, and Type Casting, Boolean Logic in WHERE (AND, OR, NOT)
Builds toward Conditional Aggregation (CASE inside Aggregates), COALESCE and NULLIF
What is CASE WHEN in SQL?
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.
How do you write a CASE WHEN expression in SQL?
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.
Does the order of WHEN branches matter in SQL?
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.
How do you handle NULL inside a CASE expression?
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:
SELECT name,
CASE WHEN category_id IS NULL THEN 'uncategorized'
WHEN category_id >= 5 THEN 'specialty'
ELSE 'general'
END AS classification
FROM productsThe 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.
What does CASE return when nothing matches and there is no ELSE?
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.'
Practice CASE WHEN in SQL
Brightlane's product team is preparing a pricing report and needs every item in the catalogue labelled by price tier.
Write a query to return each product's name, its price, and a price_tier label:
'premium'if the price is above$500.'standard'for all other prices.
Assumptions:
- The
productstable contains every product in Brightlane's catalogue. - A product priced exactly at
$500is'standard'(the threshold is strictly greater-than).
Output:
- One row per product, with columns
name,price, andprice_tier.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 CASE WHEN 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.
Start learning to practice all 10 CASE WHEN problems, with instant grading and mastery tracking.
Deeper guides on CASE WHEN
- turning rows into columns with CASE
One aggregate with a FILTER clause per column, with no extension to install.
Common questions about CASE WHEN
What happens if two WHEN branches are both true?
The first one wins, and the branches below it are not evaluated for that row. CASE tests its branches from the top and stops at the first match, which is why the most specific condition belongs first and a general catch-all belongs last. Two things escape that: a constant such as 1/0 can be evaluated when the query is planned, and an aggregate inside a branch is computed before any branch is chosen.
Can the branches of a CASE return different types?
Only when the types are compatible. PostgreSQL settles on one type for the whole expression, so an integer branch beside a decimal branch resolves to numeric, while a text branch beside a number raises an error instead of picking one.
Is there a shorter CASE form for comparing one value?
Yes. Put the expression straight after CASE and each WHEN then carries only the value to compare against, which reads better when every branch tests the same column for equality. Go back to the longer form as soon as a branch needs a range or a different column.
Can you use CASE in an ORDER BY?
Yes, and it is the usual way to impose a custom sort order. Map each value to a number inside the CASE and the rows arrive in the sequence you chose rather than alphabetically, which is how you get pending to sort before shipped.
