SQL 48 🛢️ Conditional Logic with CASE WHEN Expressions
SQL does not have if statements inside a query. It has CASE. The CASE expression is the closest thing SQL has to conditional logic, and it is remarkably flexible. It can appear in the SELECT list to produce computed columns, in the WHERE clause to filter conditionally, in the ORDER BY clause to sort by a custom order, and even in GROUP BY to group rows by a derived category. It is an expression, not a statement, which means it produces a value and can be used anywhere a value is valid.
This chapter covers CASE in full. You will learn the two syntaxes—simple and searched—and when to use each. You will see how CASE combines with aggregates to produce pivot-style reports, how it handles nulls, and how it behaves when no condition matches. You will also see the patterns that make CASE readable and the pitfalls that make it fragile.
Key point: CASE is an expression that evaluates conditions in order and returns the value associated with the first condition that is true. If no condition is true, it returns the value from the ELSE clause. If there is no ELSE clause, it returns NULL. The evaluation is short-circuiting: once a condition matches, subsequent conditions are not evaluated.
Why CASE exists
The conditional value problem. A query often needs to produce a value that depends on other values. A customer’s tier is “Gold” if they have spent more than $10,000 and “Silver” otherwise. An order’s status is “Overdue” if the due date has passed and “Pending” otherwise. Without CASE, these computations would require application code to post-process the result set. CASE moves the logic into the query, where it can be used for filtering, sorting, and aggregation.
The pivot problem. A common reporting need is to transform rows into columns—the pivot operation. A table of sales by month can be pivoted into a table with one row per product and one column per month. SQL does not have a PIVOT operator in the standard, but CASE combined with SUM or COUNT produces the same result. The pattern is to write a CASE expression for each output column, returning the value when the condition matches and zero otherwise, then aggregate.
The custom sort problem. A query may need to sort by a non-alphabetical, non-numerical order. Priority levels—”Critical,” “High,” “Medium,” “Low”—do not sort alphabetically. A CASE expression in the ORDER BY clause maps each value to a number that sorts correctly.
The null problem. CASE handles nulls explicitly. A condition like WHEN column IS NULL is valid and common. The ELSE clause provides a default for rows that do not match any condition. Without ELSE, unmatched rows produce NULL, which may or may not be the intent.
The readability problem. A CASE expression can be long. When it is, it can be hard to read. The patterns that keep it readable—aligning WHEN and THEN, using comments, extracting complex logic into a view or a subquery—are part of using it well.
a. Simple CASE and searched CASE
CASE has two forms. The simple form compares an expression to a set of values.
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE result_default
ENDThe simple form is equivalent to a series of equality comparisons. The expression is evaluated once, and each WHEN value is compared to it.
SELECT
order_id,
status,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'S' THEN 'Shipped'
WHEN 'D' THEN 'Delivered'
WHEN 'C' THEN 'Cancelled'
ELSE 'Unknown'
END AS status_label
FROM orders;The searched form uses a condition in each WHEN clause. The conditions can be any boolean expression, not just equality.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_default
ENDThe searched form is more flexible. It can express range checks, null checks, and complex conditions.
SELECT
customer_id,
total_spent,
CASE
WHEN total_spent > 10000 THEN 'Gold'
WHEN total_spent > 5000 THEN 'Silver'
WHEN total_spent > 1000 THEN 'Bronze'
ELSE 'Standard'
END AS tier
FROM customer_totals;Conditions are evaluated in order. The first condition that is true determines the result. Subsequent conditions are not evaluated. This is why the order of the WHEN clauses matters: the “Gold” condition must come before the “Silver” condition, or a customer with $15,000 would match “Silver” first if the conditions were reversed.
The ELSE clause is optional. Without it, unmatched rows produce NULL. This may be appropriate when the CASE is used to flag specific cases and NULL is a meaningful result. It may be a bug when the intent is to categorize every row.
b. CASE in SELECT, WHERE, and ORDER BY
The most common use of CASE is in the SELECT list, producing a computed column.
SELECT
product_id,
name,
price,
CASE
WHEN price < 10 THEN 'Budget'
WHEN price < 50 THEN 'Standard'
WHEN price < 200 THEN 'Premium'
ELSE 'Luxury'
END AS price_tier
FROM products;The computed column can be used in ORDER BY by its alias, in some databases:
SELECT
product_id,
name,
CASE
WHEN price < 10 THEN 'Budget'
WHEN price < 50 THEN 'Standard'
ELSE 'Premium'
END AS price_tier
FROM products
ORDER BY price_tier;CASE in the WHERE clause filters rows based on a condition. This is useful when the filter depends on a value that is computed from the row itself.
SELECT customer_id, name
FROM customers
WHERE CASE
WHEN country = 'US' THEN region IN ('East', 'West')
WHEN country = 'CA' THEN region = 'North'
ELSE true
END;This is a contrived example, but it illustrates the pattern. A more practical use is filtering based on a value that is expensive to compute once and reuse:
SELECT order_id, total
FROM orders
WHERE CASE
WHEN status = 'Cancelled' THEN false
WHEN total > 1000 THEN true
ELSE false
END;CASE in the ORDER BY clause produces a custom sort. This is useful when the natural order of a column does not match the desired order.
SELECT ticket_id, priority, description
FROM tickets
ORDER BY CASE priority
WHEN 'Critical' THEN 1
WHEN 'High' THEN 2
WHEN 'Medium' THEN 3
WHEN 'Low' THEN 4
ELSE 5
END;The tickets are sorted by priority in the intended order, not alphabetically. Without the CASE, “Critical” would sort before “High” only because of alphabetical order, and “Low” would sort before “Medium.”
c. CASE with aggregates and pivot patterns
CASE combined with aggregate functions produces pivot-style reports. The pattern is to write a CASE expression for each output column, returning the value when the condition matches and zero (or NULL) otherwise, then aggregate with SUM or COUNT.
SELECT
product_id,
SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS jan_sales,
SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS feb_sales,
SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS mar_sales
FROM sales
GROUP BY product_id;The result has one row per product and one column per month. The CASE expression converts the month values into columns. The SUM aggregates the amounts. This is the standard pivot pattern in SQL.
COUNT with CASE is used to count rows that meet a condition:
SELECT
department,
COUNT(*) AS total_employees,
COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_earners
FROM employees
GROUP BY department;The COUNT(CASE WHEN ... THEN 1 END) pattern counts only the rows where the condition is true. The ELSE clause is omitted, so non-matching rows produce NULL, and COUNT ignores NULL. This is equivalent to SUM(CASE WHEN ... THEN 1 ELSE 0 END).
SUM with CASE is used to sum values conditionally:
SELECT
customer_id,
SUM(CASE WHEN status = 'Completed' THEN amount ELSE 0 END) AS completed_total,
SUM(CASE WHEN status = 'Pending' THEN amount ELSE 0 END) AS pending_total
FROM orders
GROUP BY customer_id;The result shows the total completed and pending amounts per customer. The CASE expression filters the amounts by status before aggregation.
The null behavior of CASE in aggregates is important. SUM(CASE WHEN ... THEN amount END) with no ELSE returns NULL for groups where no rows match, because SUM of all NULL is NULL. Adding ELSE 0 makes it return zero. The choice depends on whether zero or NULL is the correct representation of “no matching rows.”
Complete Example Session
-- ============================================
-- PART 1: SIMPLE CASE
-- ============================================
SELECT
order_id,
status,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'S' THEN 'Shipped'
WHEN 'D' THEN 'Delivered'
ELSE 'Unknown'
END AS status_label
FROM orders;-- ============================================
-- PART 2: SEARCHED CASE
-- ============================================
SELECT
customer_id,
total_spent,
CASE
WHEN total_spent > 10000 THEN 'Gold'
WHEN total_spent > 5000 THEN 'Silver'
WHEN total_spent > 1000 THEN 'Bronze'
ELSE 'Standard'
END AS tier
FROM customer_totals;-- ============================================
-- PART 3: CASE WITHOUT ELSE
-- ============================================
SELECT
product_id,
CASE WHEN price > 100 THEN 'Expensive' END AS flag
FROM products;
-- Rows with price <= 100 produce NULL-- ============================================
-- PART 4: CASE WITH NULL CHECK
-- ============================================
SELECT
employee_id,
CASE
WHEN manager_id IS NULL THEN 'Top Level'
ELSE 'Reports to Manager'
END AS level
FROM employees;-- ============================================
-- PART 5: CASE IN ORDER BY
-- ============================================
SELECT ticket_id, priority, description
FROM tickets
ORDER BY CASE priority
WHEN 'Critical' THEN 1
WHEN 'High' THEN 2
WHEN 'Medium' THEN 3
WHEN 'Low' THEN 4
ELSE 5
END;-- ============================================
-- PART 6: CASE IN WHERE
-- ============================================
SELECT order_id, total
FROM orders
WHERE CASE
WHEN status = 'Cancelled' THEN false
ELSE total > 1000
END;-- ============================================
-- PART 7: PIVOT WITH SUM AND CASE
-- ============================================
SELECT
product_id,
SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS jan,
SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS feb,
SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS mar
FROM sales
GROUP BY product_id;-- ============================================
-- PART 8: COUNT WITH CASE
-- ============================================
SELECT
department,
COUNT(*) AS total,
COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners
FROM employees
GROUP BY department;-- ============================================
-- PART 9: NESTED CASE
-- ============================================
SELECT
order_id,
CASE
WHEN status = 'Shipped' THEN
CASE
WHEN delivered_date IS NULL THEN 'In Transit'
ELSE 'Delivered'
END
ELSE 'Not Shipped'
END AS shipping_status
FROM orders;-- ============================================
-- PART 10: CASE IN GROUP BY
-- ============================================
SELECT
CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group,
COUNT(*) AS count
FROM users
GROUP BY CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END;The ten parts covered simple CASE, searched CASE, CASE without ELSE, null checks, CASE in ORDER BY, CASE in WHERE, pivot with SUM and CASE, COUNT with CASE, nested CASE, and CASE in GROUP BY.
Quick Reference
CASE Forms
| Form | Syntax | Use Case |
|---|---|---|
| Simple | CASE expr WHEN val THEN result END | Equality comparisons |
| Searched | CASE WHEN cond THEN result END | Any condition |
CASE Clauses
| Clause | Required | Purpose |
|---|---|---|
WHEN | Yes (at least one) | Condition to evaluate |
THEN | Yes | Value if condition is true |
ELSE | No | Value if no condition is true |
END | Yes | Terminates the expression |
CASE in Query Clauses
| Clause | Purpose |
|---|---|
SELECT | Computed column |
WHERE | Conditional filter |
ORDER BY | Custom sort |
GROUP BY | Custom grouping |
HAVING | Conditional aggregate filter |
Pivot Patterns
| Pattern | Purpose |
|---|---|
SUM(CASE WHEN ... THEN amount ELSE 0 END) | Conditional sum |
COUNT(CASE WHEN ... THEN 1 END) | Conditional count |
AVG(CASE WHEN ... THEN value END) | Conditional average |
MAX(CASE WHEN ... THEN value END) | Conditional max |
Null Behavior
| Situation | Result |
|---|---|
No condition matches, no ELSE | NULL |
No condition matches, ELSE value | value |
Condition is NULL | Treated as false |
SUM(CASE ... END) no match | NULL |
SUM(CASE ... ELSE 0 END) no match | 0 |
Best Practices
✅ Do This:
-- Use searched CASE for ranges
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END -- ✅
-- Order WHEN clauses from most specific to least
CASE
WHEN amount > 10000 THEN 'Large'
WHEN amount > 1000 THEN 'Medium'
ELSE 'Small'
END -- ✅
-- Use ELSE for explicit default
CASE WHEN x THEN 'yes' ELSE 'no' END -- ✅
-- Use CASE in ORDER BY for custom sort
ORDER BY CASE priority WHEN 'High' THEN 1 ELSE 2 END -- ✅
-- Use COUNT(CASE WHEN ... THEN 1 END) for conditional count
COUNT(CASE WHEN status = 'Active' THEN 1 END) -- ✅
-- Align WHEN and THEN for readability
CASE
WHEN a THEN 1
WHEN b THEN 2
ELSE 3
END -- ✅❌ Don’t Do This:
-- Don't reverse the order of range conditions
CASE
WHEN amount > 1000 THEN 'Large' -- matches first
WHEN amount > 10000 THEN 'Huge' -- unreachable
END -- ❌
-- Don't forget ELSE when every row needs a value
CASE WHEN x THEN 'yes' END -- ⚠️ NULL for non-matches
-- Don't repeat complex expressions
CASE
WHEN expensive_function(a) > 10 THEN 'A'
WHEN expensive_function(a) > 5 THEN 'B'
END -- ⚠️ computed twice
-- Don't use CASE for simple null substitution
CASE WHEN x IS NULL THEN 0 ELSE x END -- ⚠️ use COALESCE
-- Don't nest deeply without refactoring
CASE WHEN a THEN CASE WHEN b THEN CASE WHEN c THEN 1 END END END -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
Unreachable WHEN | Conditions ordered incorrectly | Most specific first |
NULL result | No ELSE clause | Add ELSE |
SUM returns NULL | No ELSE 0 | Add ELSE 0 |
| Condition never true | NULL comparison | Use IS NULL |
CASE in GROUP BY mismatch | Different expressions | Repeat exact expression |
| Type mismatch | THEN values different types | Cast to common type |
| Verbose query | Repeated complex conditions | Use a subquery or CTE |
Real-World Examples
1. Status Label
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'S' THEN 'Shipped'
WHEN 'D' THEN 'Delivered'
ELSE 'Unknown'
END2. Price Tier
CASE
WHEN price < 10 THEN 'Budget'
WHEN price < 50 THEN 'Standard'
WHEN price < 200 THEN 'Premium'
ELSE 'Luxury'
END3. Age Group
CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END4. Custom Sort
ORDER BY CASE priority
WHEN 'Critical' THEN 1
WHEN 'High' THEN 2
ELSE 3
END5. Pivot by Month
SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS jan6. Conditional Count
COUNT(CASE WHEN status = 'Active' THEN 1 END) AS active_count7. Null Handling
CASE
WHEN manager_id IS NULL THEN 'Top'
ELSE 'Reports to Manager'
END8. Conditional Sum
SUM(CASE WHEN type = 'Credit' THEN amount ELSE 0 END) AS credits9. Nested Condition
CASE
WHEN status = 'Active' THEN
CASE WHEN balance > 0 THEN 'Active with Balance'
ELSE 'Active Zero Balance' END
ELSE 'Inactive'
END10. Group by Derived Category
GROUP BY CASE
WHEN age < 18 THEN 'Minor'
ELSE 'Adult'
ENDVisual
CASE Evaluation Flow
┌─────────────────────────────────────────────────────────────┐
│ CASE EVALUATION │
│ │
│ CASE │
│ WHEN condition1 THEN result1 │
│ WHEN condition2 THEN result2 │
│ WHEN condition3 THEN result3 │
│ ELSE result_default │
│ END │
│ │
│ 1. Evaluate condition1 │
│ ├── TRUE ──▶ result1, stop │
│ └── FALSE ──▶ continue │
│ 2. Evaluate condition2 │
│ ├── TRUE ──▶ result2, stop │
│ └── FALSE ──▶ continue │
│ 3. Evaluate condition3 │
│ ├── TRUE ──▶ result3, stop │
│ └── FALSE ──▶ continue │
│ 4. No condition matched ──▶ result_default │
│ │
│ First true condition wins. Order matters. │
│ │
└─────────────────────────────────────────────────────────────┘Simple vs Searched CASE
┌─────────────────────────────────────────────────────────────┐
│ SIMPLE CASE │
│ │
│ CASE status │
│ WHEN 'P' THEN 'Pending' │
│ WHEN 'S' THEN 'Shipped' │
│ ELSE 'Unknown' │
│ END │
│ │
│ Equivalent to: │
│ IF status = 'P' THEN 'Pending' │
│ ELSE IF status = 'S' THEN 'Shipped' │
│ ELSE 'Unknown' │
│ │
│ Compares one expression to multiple values. │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ SEARCHED CASE │
│ │
│ CASE │
│ WHEN amount > 10000 THEN 'Gold' │
│ WHEN amount > 5000 THEN 'Silver' │
│ ELSE 'Bronze' │
│ END │
│ │
│ Each WHEN is an independent condition. │
│ Can use ranges, null checks, and complex logic. │
│ │
└─────────────────────────────────────────────────────────────┘Pivot Pattern
┌─────────────────────────────────────────────────────────────┐
│ ORIGINAL TABLE │
│ │
│ product │ month │ amount │
│ ────────┼───────┼─────── │
│ A │ Jan │ 100 │
│ A │ Feb │ 200 │
│ A │ Mar │ 150 │
│ B │ Jan │ 50 │
│ B │ Feb │ 75 │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ PIVOTED WITH CASE + SUM │
│ │
│ SELECT product_id, │
│ SUM(CASE WHEN month='Jan' THEN amount ELSE 0 END) AS jan,│
│ SUM(CASE WHEN month='Feb' THEN amount ELSE 0 END) AS feb,│
│ SUM(CASE WHEN month='Mar' THEN amount ELSE 0 END) AS mar │
│ FROM sales GROUP BY product_id; │
│ │
│ product │ jan │ feb │ mar │
│ ────────┼─────┼─────┼───── │
│ A │ 100 │ 200 │ 150 │
│ B │ 50 │ 75 │ 0 │
│ │
│ Rows become columns. Aggregation fills the cells. │
│ │
└─────────────────────────────────────────────────────────────┘ORDER BY Custom Sort
┌─────────────────────────────────────────────────────────────┐
│ WITHOUT CASE (ALPHABETICAL) │
│ │
│ ORDER BY priority │
│ │
│ Critical │
│ High │
│ Low │
│ Medium │
│ │
│ Alphabetical, not logical. │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ WITH CASE (LOGICAL) │
│ │
│ ORDER BY CASE priority │
│ WHEN 'Critical' THEN 1 │
│ WHEN 'High' THEN 2 │
│ WHEN 'Medium' THEN 3 │
│ WHEN 'Low' THEN 4 │
│ END │
│ │
│ Critical │
│ High │
│ Medium │
│ Low │
│ │
│ Logical order. │
│ │
└─────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
Simple CASE | CASE expr WHEN val THEN result END |
Searched CASE | CASE WHEN cond THEN result END |
ELSE | Optional default |
No ELSE | Returns NULL for unmatched rows |
| Evaluation order | First true condition wins |
In SELECT | Computed column |
In WHERE | Conditional filter |
In ORDER BY | Custom sort |
In GROUP BY | Derived grouping |
| Pivot pattern | SUM(CASE WHEN ... THEN ... ELSE 0 END) |
| Conditional count | COUNT(CASE WHEN ... THEN 1 END) |
| Null handling | WHEN col IS NULL |
Key takeaways:
CASEis an expression, not a statement. It produces a value and can be used anywhere a value is valid: in theSELECTlist, inWHERE, inORDER BY, inGROUP BY, and inside aggregate functions.- The simple form compares one expression to values. The searched form evaluates independent conditions. Use simple for equality comparisons, searched for ranges and complex logic.
- Evaluation is ordered and short-circuits. The first true condition wins. Subsequent conditions are not evaluated. This is why the order of
WHENclauses matters: most specific first, most general last. ELSEprovides a default. Without it, unmatched rows produceNULL. This may be correct or a bug depending on the intent. AddELSEwhen every row should have a value.- The pivot pattern uses
CASEwithSUMorCOUNT. Each output column is aCASEexpression that returns the value when the condition matches and zero (orNULL) otherwise. Aggregation combines the values. COUNT(CASE WHEN ... THEN 1 END)counts conditionally. TheELSEis omitted, so non-matching rows produceNULL, andCOUNTignoresNULL. This is the standard pattern for conditional counts.- The null behavior of
CASEin aggregates matters.SUM(CASE WHEN ... THEN amount END)with noELSEreturnsNULLfor groups with no matching rows. AddingELSE 0returns zero. - Null comparisons need
IS NULL.CASE WHEN col = NULLnever matches, becausecol = NULLisNULL, not true. UseCASE WHEN col IS NULL.
Remember: CASE is SQL’s conditional expression. It brings if-then-else logic into the query, where it can be used for computed columns, conditional filters, custom sorts, and pivot reports. The searched form is more flexible and more common. The simple form is shorter when comparing one expression to a list of values. The order of WHEN clauses matters because evaluation short-circuits. The ELSE clause provides a default and should be included when every row needs a value. Combined with aggregates, CASE produces the pivot pattern that turns rows into columns. It is one of the most versatile expressions in SQL.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!