Sooner or later every report needs a little logic. Show “Paid” instead of the status code 1. Label orders as small, medium or large. Count how many customers are from each city in one row. Sort by a custom order that isn’t alphabetical. All of that comes down to one tool: the CASE expression.
If you know IF ... THEN ... ELSE from Excel or any programming language, you already understand the idea. CASE is SQL’s version, and it works almost everywhere in a query.

Table of Contents
The Sample Data
| order_id | customer | amount | status |
|---|---|---|---|
| 1 | Sara | 650 | 1 |
| 2 | Omar | 120 | 2 |
| 3 | Lina | 45 | 1 |
| 4 | Ali | 300 | 3 |
| 5 | Huda | 80 | 1 |
Status codes: 1 = Paid, 2 = Pending, 3 = Cancelled.
Searched CASE: Conditions in Order
This is the form you’ll use most. Each WHEN has its own condition:
SELECT order_id, customer, amount,
CASE
WHEN amount >= 500 THEN 'Large'
WHEN amount >= 100 THEN 'Medium'
ELSE 'Small'
END AS order_size
FROM orders;
| order_id | customer | amount | order_size |
|---|---|---|---|
| 1 | Sara | 650 | Large |
| 2 | Omar | 120 | Medium |
| 3 | Lina | 45 | Small |
| 4 | Ali | 300 | Medium |
| 5 | Huda | 80 | Small |
The key rule: SQL checks the WHEN lines from top to bottom and stops at the first one that’s true. Sara’s 650 is also greater than 100, but she’s already been labelled “Large” by then. That’s why you put the strictest condition first.
If no condition matches and there’s no ELSE, the result is NULL. I almost always include an ELSE, even if it’s just ELSE 'Unknown', so gaps in my logic are easy to spot.
Simple CASE: Matching One Value
When you’re comparing a single column to a list of exact values, there’s a shorter form:
SELECT order_id, customer,
CASE status
WHEN 1 THEN 'Paid'
WHEN 2 THEN 'Pending'
WHEN 3 THEN 'Cancelled'
ELSE 'Unknown'
END AS status_name
FROM orders;
It reads nicely, but it can only test equality. For ranges, NULL checks or several columns, use the searched form.
A tip from real projects: if you find yourself writing the same status CASE in many queries, the codes probably deserve their own lookup table that you can JOIN to. CASE is best for logic that’s specific to one report.
CASE Inside Aggregates (Conditional Counting)
This trick is one of the most useful things in SQL. Put a CASE inside SUM or COUNT to count different things in one pass:
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS paid_orders,
SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS pending_orders,
SUM(CASE WHEN status = 1 THEN amount ELSE 0 END) AS paid_amount
FROM orders;
| total_orders | paid_orders | pending_orders | paid_amount |
|---|---|---|---|
| 5 | 3 | 1 | 775 |
Paid orders are Sara (650), Lina (45) and Huda (80), so the paid amount is 775. Combine this with GROUP BY and you get a pivot-style report, like one row per month with a column per status, without any special pivot syntax.
CASE in ORDER BY: Custom Sorting
Want pending orders at the top, then paid, then cancelled? Alphabetical won’t do it, but CASE can:
SELECT order_id, customer, status
FROM orders
ORDER BY CASE status
WHEN 2 THEN 1 -- Pending first
WHEN 1 THEN 2 -- then Paid
WHEN 3 THEN 3 -- Cancelled last
END,
order_id;
CASE in WHERE and UPDATE
You can use CASE anywhere an expression is allowed. A common one is an update that applies different rules to different rows:
-- Different price increases by category
UPDATE products
SET price = price * CASE category
WHEN 'Books' THEN 1.05
WHEN 'Stationery' THEN 1.10
ELSE 1.00
END;
In a WHERE clause, plain AND/OR logic is usually clearer than CASE, and it lets the database use indexes more easily. Keep CASE for the SELECT list, aggregates and sorting.
Watch Out for NULL
A condition like WHEN discount = NULL is never true. Test for null with IS NULL:
CASE
WHEN discount IS NULL THEN 'No discount'
WHEN discount >= 20 THEN 'Big discount'
ELSE 'Small discount'
END
Nulls catch everyone at least once. There’s a whole article on them: NULL in SQL Explained Simply.
CASE WHEN with Multiple Conditions
Each WHEN can combine several conditions with AND and OR, just like a WHERE clause. Here the orders also have an order_date column:
SELECT order_id,
CASE
WHEN status = 1 AND amount >= 500 THEN 'Paid - large'
WHEN status = 1 THEN 'Paid'
WHEN status = 2 OR order_date < '2026-01-15' THEN 'Follow up'
ELSE 'Other'
END AS label
FROM orders;
The “first true WHEN wins” rule matters even more here. A paid order of 650 matches both of the first two lines, and gets “Paid – large” only because that line comes first. When you mix AND and OR in one WHEN, add brackets so the meaning is obvious: WHEN status = 2 AND (amount > 100 OR customer = 'Omar').
Nested CASE
A THEN can contain another CASE. It works, but more than one level gets hard to read quickly:
CASE
WHEN status = 1 THEN
CASE WHEN amount >= 500 THEN 'Paid - large' ELSE 'Paid' END
WHEN status = 2 THEN 'Pending'
ELSE 'Cancelled'
END
Most nested CASE expressions can be flattened into one level with AND, like the example above it. Flat is usually easier to check.
CASE vs IIF, IF() and COALESCE
Some databases have shorter functions for simple cases. They are handy, but CASE works everywhere, so it’s the one to learn first.
| You want | Short form | Same thing with CASE |
|---|---|---|
| Two outcomes (SQL Server 2012+) | IIF(amount >= 500, 'Large', 'Small') | CASE WHEN amount >= 500 THEN 'Large' ELSE 'Small' END |
| Two outcomes (MySQL) | IF(amount >= 500, 'Large', 'Small') | Same as above |
| First value that isn’t NULL | COALESCE(phone, mobile, 'No phone') | CASE WHEN phone IS NOT NULL THEN phone WHEN mobile IS NOT NULL THEN mobile ELSE 'No phone' END |
| NULL instead of a value | NULLIF(qty, 0) | CASE WHEN qty = 0 THEN NULL ELSE qty END |
NULLIF is the classic way to avoid “divide by zero” errors: total / NULLIF(qty, 0) returns NULL instead of failing when qty is 0. PostgreSQL has no IIF or IF() function, so use CASE there.
CASE in Real Reports
In business reporting, CASE does a lot of the heavy lifting. These patterns appear in almost every sales or accounting system.
One column per month
Management often wants months across the top instead of down the side. Conditional SUM does it without any special pivot syntax, and works in every database:
SELECT YEAR(order_date) AS yr,
SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS jan,
SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS feb,
SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS mar
FROM orders
WHERE status <> 3 -- leave out cancelled orders
GROUP BY YEAR(order_date);
| yr | jan | feb | mar |
|---|---|---|---|
| 2026 | 770.00 | 45.00 | 80.00 |
YEAR() and MONTH() work in SQL Server and MySQL; in PostgreSQL use EXTRACT(MONTH FROM order_date). SQL Server also has a PIVOT operator, compared in PIVOT and UNPIVOT in SQL Server, but the CASE version is easier to extend with extra totals.
Group by a CASE (bands and buckets)
To count orders per size band, you need to GROUP BY the CASE result. The cleanest way is to compute the band in a subquery, then group by its name:
SELECT order_size, COUNT(*) AS orders, SUM(amount) AS total
FROM (
SELECT amount,
CASE
WHEN amount >= 500 THEN 'Large'
WHEN amount >= 100 THEN 'Medium'
ELSE 'Small'
END AS order_size
FROM orders
) AS t
GROUP BY order_size
ORDER BY total DESC;
| order_size | orders | total |
|---|---|---|
| Large | 1 | 650.00 |
| Medium | 2 | 420.00 |
| Small | 2 | 125.00 |
SQL Server doesn’t let you group by a column alias, so without the subquery you would have to repeat the whole CASE in GROUP BY. (MySQL and PostgreSQL accept GROUP BY order_size directly, but the subquery works everywhere.) The same pattern builds customer aging reports with “0–30 days”, “31–60 days” and “over 90 days” buckets; see the SQL Server aging report guide.
Debit and credit columns
Many accounting systems store one signed amount per journal line: positive for debit, negative for credit. Reports and trial balances need two separate columns:
SELECT account,
SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS debit,
SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) AS credit
FROM gl_lines
GROUP BY account;
The credit branch flips the sign so both columns show positive numbers, which is how accountants expect to read them.
Keep every branch the same data type
This query looks harmless, but in SQL Server it fails as soon as one row reaches the ELSE branch:
SELECT CASE WHEN amount > 0 THEN amount ELSE 'N/A' END
FROM orders;
-- Error converting data type varchar to numeric.
All branches of a CASE must return one data type. SQL Server picks the type with the highest precedence (here the number) and tries to convert 'N/A' to it. Either return NULL and show “N/A” in the application, or convert the number to text in every branch with CAST(amount AS VARCHAR(20)).
Two Forms Side by Side
| Simple CASE | Searched CASE | |
|---|---|---|
| Syntax | CASE col WHEN value THEN ... | CASE WHEN condition THEN ... |
| Can test | Equality with one column | Any condition: ranges, several columns, IS NULL |
| Best for | Translating codes into labels | Grouping, bands and business rules |
Common Mistakes
- Putting conditions in the wrong order, so a broad
WHENcatches rows meant for a later one. - Forgetting
END. EveryCASEneeds one. - Returning different data types from different branches, like a number in one and text in another. Keep them all the same type.
- Leaving out
ELSEand then being surprised byNULLresults.
Frequently Asked Questions
What is the difference between CASE and IF in SQL?
CASE is standard SQL and works in every database, inside any query. IF() in MySQL and IIF() in SQL Server are shorter functions for exactly two outcomes. SQL Server and MySQL also have an IF ... ELSE statement, but that’s for controlling the flow of a script or stored procedure, not for values inside a SELECT.
Can I use CASE in a WHERE clause?
Yes, for example WHERE CASE WHEN @Type = 'A' THEN amount ELSE 0 END > 100. But it often stops the database from using an index, and plain AND / OR is usually clearer. Use CASE in WHERE only when the logic can’t be written another way.
Can one CASE return two columns?
No. A CASE expression returns a single value. If two columns depend on the same condition, write two CASE expressions, or move the logic into a lookup table and JOIN to it.
Is there a limit to how many WHEN lines I can use?
Not in practice: you can have dozens. But if a CASE grows to many lines that translate codes into names, a lookup table is easier to maintain, and can be changed without touching every query.
Conclusion
CASE brings if/then/else logic into SQL. Use the searched form for conditions, the simple form for mapping codes to labels, and remember that the first matching WHEN wins. Inside SUM and COUNT it becomes a reporting superpower.
Take one of your existing reports and see whether a few CASE expressions could replace several separate queries. They usually can.
Related articles
- SQL WHERE, ORDER BY and LIMIT: filter, sort and limit your results.
- SQL Window Functions: ROW_NUMBER, RANK, LAG and running totals.
- HAVING vs WHERE in SQL: filtering rows versus filtering groups.
- SQL Date Functions Explained Simply: GETDATE, DATEADD, DATEDIFF and more.
- PIVOT and UNPIVOT in SQL Server: the built-in syntax for the pivot-style reports you can also build with CASE.