CASE WHEN is how you write IF logic in SQL. With it you can turn numbers into labels ("High", "Medium", "Low"), build conditional totals, sort in a custom order and clean messy values, all inside one query.
This guide covers the two forms of CASE, eight practical examples you will use at work, and the mistakes that give wrong results. The syntax works in MySQL, PostgreSQL, SQL Server, Oracle and SQLite.
Quick answer
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
- SQL checks the WHEN conditions from top to bottom and returns the result of the first one that is true.
- If no condition is true and there is no ELSE, the result is NULL.
- CASE can be used in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregate functions such as SUM and COUNT.
Sample table
The examples use an orders table:
| order_id | customer | region | status | amount |
|---|---|---|---|---|
| 1 | Asha | North | Delivered | 12000 |
| 2 | Rahul | South | Cancelled | 4500 |
| 3 | Meena | North | Delivered | 56000 |
| 4 | Karan | East | Pending | 8000 |
| 5 | Priya | South | Delivered | 23000 |
| 6 | Vikas | West | Returned | 15000 |
The two forms of CASE
Searched CASE (most common)
Each WHEN has its own full condition, so you can use >, BETWEEN, LIKE, IN, AND and OR.
CASE WHEN amount >= 50000 THEN 'High' ELSE 'Normal' END
Simple CASE
Compares one column with a list of exact values. It is shorter but can only test equality.
CASE region
WHEN 'North' THEN 'N'
WHEN 'South' THEN 'S'
ELSE 'Other'
END
8 practical examples
1. Group values into bands
SELECT order_id,
amount,
CASE
WHEN amount >= 50000 THEN 'High'
WHEN amount >= 10000 THEN 'Medium'
ELSE 'Low'
END AS order_size
FROM orders;
Order matters. Order 3 (56,000) stops at "High". If you put amount >= 10000 first, every large order would be labelled "Medium".
2. Count by condition in one row
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;
Result: 6 total, 3 delivered, 1 cancelled. This pattern is called conditional aggregation and is the most useful CASE trick for reports.
3. Conditional totals by group
SELECT region,
SUM(CASE WHEN status = 'Delivered' THEN amount ELSE 0 END) AS delivered_sales,
SUM(amount) AS all_sales
FROM orders
GROUP BY region;
North shows 68,000 delivered out of 68,000; South shows 23,000 delivered out of 27,500.
4. Pivot rows into columns
SELECT status,
SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS north,
SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS south,
SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) AS east,
SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS west
FROM orders
GROUP BY status;
This works in every database, unlike the PIVOT keyword, which only some databases support.
5. Percentage of a condition
SELECT
ROUND(100.0 * SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) / COUNT(*), 1)
AS delivery_rate_pct
FROM orders;
Result: 50.0. Multiply by 100.0, not 100, so the division is not done with whole numbers (in some databases 3/6 would give 0).
6. Custom sort order
SELECT order_id, status
FROM orders
ORDER BY CASE status
WHEN 'Pending' THEN 1
WHEN 'Delivered' THEN 2
WHEN 'Returned' THEN 3
ELSE 4
END;
Pending orders come first, which alphabetical sorting cannot do.
7. Clean and standardise values
SELECT customer,
CASE
WHEN UPPER(TRIM(region)) IN ('N', 'NORTH') THEN 'North'
WHEN UPPER(TRIM(region)) IN ('S', 'SOUTH') THEN 'South'
ELSE region
END AS region_clean
FROM orders;
Useful when the same value is typed in different ways in the source system.
8. CASE in WHERE
SELECT *
FROM orders
WHERE amount >= CASE WHEN region = 'North' THEN 20000 ELSE 10000 END;
Each region gets a different minimum amount. Often a plain OR is easier to read, so use this only when the rule is truly different per group.
Using the CASE result in GROUP BY
To count orders per band, repeat the expression in GROUP BY or use a subquery:
SELECT order_size, COUNT(*) AS orders
FROM (
SELECT CASE
WHEN amount >= 50000 THEN 'High'
WHEN amount >= 10000 THEN 'Medium'
ELSE 'Low'
END AS order_size
FROM orders
) t
GROUP BY order_size;
Result: High 1, Medium 3, Low 2. Most databases do not let you use the alias order_size directly in GROUP BY of the same query (MySQL and PostgreSQL do).
Common mistakes
- Wrong order of conditions: put the most specific or highest band first.
- Forgetting ELSE: missing rows become NULL, and NULL is ignored by SUM and COUNT(column).
- Mixed data types:
THEN 'High' ELSE 0mixes text and a number and fails in strict databases. Return the same type in every branch. - Testing NULL with =:
WHEN discount = NULLis never true. UseWHEN discount IS NULL. - Missing END: every CASE needs its own END, including nested ones.
CASE vs IF vs IIF
MySQL has IF(condition, a, b) and SQL Server has IIF(condition, a, b). They are short, but they only exist in those databases. CASE is standard SQL and works everywhere, so it is the safer habit.
Frequently asked questions
Can I use multiple conditions in one WHEN?
Yes: WHEN region = 'North' AND amount > 10000 THEN ....
Can CASE be nested?
Yes, a THEN or ELSE can contain another CASE. Keep nesting shallow so the query stays readable.
Does CASE slow down a query?
Usually not noticeably. It is evaluated row by row like any other expression.
Practice task
Write one query that returns, for each region, the number of orders, the number of delivered orders and the delivery rate in percent. Then sort the result so the region with the lowest rate comes first.
Next, learn how to summarise data with SQL GROUP BY and HAVING, combine tables with SQL JOINs, and rank rows with SQL window functions.
