Learn SQL CASE WHEN with practical examples. Master simple CASE, searched CASE, CASE with aggregates, and common patterns every SQL developer needs.
Jump into the SQLNoir case files and put these tips to work.
SQL CASE WHEN is the if-then-else of SQL. It lets you add decision-making logic directly inside your queries, transforming, categorizing, and filtering data based on conditions you define.
SQL has two CASE syntax forms. They look similar but handle different situations. Simple CASE compares one expression against multiple values (like a switch statement). Searched CASE evaluates independent boolean conditions (like an if-else chain).
SELECT case_id, status,
CASE status
WHEN 'open' THEN 'Active Investigation'
WHEN 'closed' THEN 'Case Resolved'
WHEN 'cold' THEN 'Inactive'
ELSE 'Unknown'
END AS status_label
FROM crime_cases;Simple CASE checks status against each WHEN value using equality (=). Clean and readable for straightforward lookups.
SELECT suspect_id, name, prior_offenses,
CASE
WHEN prior_offenses > 5 THEN 'High Risk'
WHEN prior_offenses > 2 THEN 'Medium Risk'
WHEN prior_offenses > 0 THEN 'Low Risk'
ELSE 'Clean Record'
END AS risk_level
FROM suspects;Searched CASE evaluates each WHEN as a standalone condition. Supports ranges, comparisons, LIKE, IN, IS NULL, and anything else you can put in a WHERE clause.
| Feature | Simple CASE | Searched CASE |
|---|---|---|
| Syntax | CASE expr WHEN val THEN... | CASE WHEN condition THEN... |
| Comparison type | Equality only (=) | Any comparison (>, <, LIKE, IN, IS NULL) |
| Multiple columns? | No (single expression) | Yes (any columns) |
| Range checks? | No | Yes (BETWEEN, >, <) |
| Best for | Status codes, enum values | Complex business logic |
| Readability | Cleaner for simple lookups | More explicit conditions |
When to use each CASE form
CASE evaluates WHEN conditions top to bottom and returns the result for the first match. Once a condition is true, it skips everything below. This means the order of your WHEN clauses matters.
CASE evaluates top-to-bottom and stops at the first match
-- ❌ WRONG: General condition catches everything first
SELECT name, prior_offenses,
CASE
WHEN prior_offenses > 0 THEN 'Some Risk'
WHEN prior_offenses > 5 THEN 'High Risk' -- Never reached!
END AS risk_level
FROM suspects;
-- ✅ RIGHT: Specific conditions first
SELECT name, prior_offenses,
CASE
WHEN prior_offenses > 5 THEN 'High Risk'
WHEN prior_offenses > 0 THEN 'Some Risk'
ELSE 'Clean Record'
END AS risk_level
FROM suspects;In the wrong version, a suspect with 8 prior offenses matches > 0 first and gets "Some Risk" instead of "High Risk." Always put specific conditions before general ones.
CASE WHEN score > 0 THEN 'some' WHEN score > 100 THEN 'high' will never reach 'high' because > 0 catches everything first.CASE WHEN appears in almost every real SQL query. If you want to practice writing conditional logic hands-on, SQLNoir's detective cases let you classify evidence and filter suspects using the same patterns.
The most common use of CASE WHEN is creating new computed columns. Bucketing continuous values into categories, mapping status codes to readable labels, or flagging rows that meet certain criteria.
CASEWHEN victim_count > 3THEN 'Critical'WHEN victim_count > 1THEN 'Major'ELSE 'Minor'END AS severityAnatomy of a CASE WHEN expression
SELECT
case_id,
location,
victim_count,
CASE
WHEN victim_count > 3 THEN 'Critical'
WHEN victim_count > 1 THEN 'Major'
ELSE 'Minor'
END AS severity
FROM crime_scenes
ORDER BY victim_count DESC;Each row gets a severity label based on victim_count. Always use AS alias after END to name the computed column.
Put CASE inside aggregate functions like COUNT or SUM to conditionally aggregate data. This is the standard way to pivot rows into columns, and it works in every SQL dialect.
SELECT district, status, COUNT(*) FROM crime_cases GROUP BY district, status;
•Multiple rows per district
•Hard to compare across statuses
•Needs post-processing for dashboards
SELECT district, COUNT(CASE WHEN status = 'open' THEN 1 END) AS open_cases, COUNT(CASE WHEN status = 'closed' THEN 1 END) AS closed_cases, COUNT(CASE WHEN status = 'cold' THEN 1 END) AS cold_cases FROM crime_cases GROUP BY district;
•One row per district
•Easy to compare statuses side by side
•Dashboard-ready output
The pivot pattern: turn row values into columns using CASE inside COUNT
SELECT district, SUM(CASE WHEN status = 'closed' THEN evidence_count ELSE 0 END) AS solved_evidence, SUM(CASE WHEN status = 'open' THEN evidence_count ELSE 0 END) AS unsolved_evidence FROM crime_cases GROUP BY district;
SUM with CASE lets you sum different subsets in the same query. Use ELSE 0 with SUM to avoid NULL in the totals.
THEN 1 END (no ELSE needed, COUNT ignores NULLs). With SUM, use ELSE 0 END to avoid NULL results when no rows match.CASE is not limited to SELECT. You can use it in ORDER BY for custom sorting, GROUP BY for computed categories, and even WHERE for conditional filtering (though you rarely should).
-- Show armed suspects first, then by name
SELECT name, status
FROM suspects
ORDER BY
CASE WHEN status = 'armed' THEN 0
WHEN status = 'wanted' THEN 1
ELSE 2
END,
name ASC;CASE in ORDER BY lets you define a custom priority. Armed suspects appear first regardless of alphabetical order.
SELECT
CASE
WHEN date_opened >= '2025-01-01' THEN 'Recent'
WHEN date_opened >= '2020-01-01' THEN 'Older'
ELSE 'Cold Case'
END AS case_age,
COUNT(*) AS total
FROM crime_cases
GROUP BY
CASE
WHEN date_opened >= '2025-01-01' THEN 'Recent'
WHEN date_opened >= '2020-01-01' THEN 'Older'
ELSE 'Cold Case'
END;The CASE expression must be repeated in both SELECT and GROUP BY (or use a subquery/CTE to avoid repetition).
WHERE CASE WHEN type = 'A' THEN 1 ELSE 0 END = 1, just write WHERE type = 'A'. CASE in WHERE is a code smell. Use it only when the filtering logic genuinely depends on other column values.The diamond vanished from a charity gala. Can you use CASE WHEN to classify guest behavior and identify the thief?
Start InvestigationYou can nest CASE inside CASE, but deep nesting gets hard to read fast. When you find yourself going more than two levels deep, flatten the conditions or use a CTE with a lookup table instead.
CASE WHEN type = 'A'
THEN CASE WHEN status = 'active'
THEN CASE WHEN priority > 5
THEN 'urgent-A'
ELSE 'normal-A'
END
ELSE 'inactive-A'
END
WHEN type = 'B' THEN ...
END•3 levels deep
•Hard to read and debug
•Adding a new type requires editing nested structure
CASE WHEN type = 'A' AND status = 'active' AND priority > 5 THEN 'urgent-A' WHEN type = 'A' AND status = 'active' THEN 'normal-A' WHEN type = 'A' THEN 'inactive-A' WHEN type = 'B' THEN ... END
•Flat structure, easy to scan
•Each outcome on one line
•Adding new cases is straightforward
Flatten nested CASE by combining conditions with AND
-- CASE for explicit NULL handling
SELECT name, phone,
CASE
WHEN phone IS NULL THEN 'No phone on file'
ELSE phone
END AS contact_phone
FROM suspects;
-- COALESCE is a shortcut for this exact pattern
SELECT name, COALESCE(phone, 'No phone on file') AS contact_phone
FROM suspects;COALESCE returns the first non-NULL value. When your CASE just replaces NULL with a default, use COALESCE instead.
Three mistakes trip up almost everyone who writes CASE WHEN queries. Here they are with fixes.
-- ❌ General before specific
CASE WHEN score > 0 THEN 'Has Score'
WHEN score > 100 THEN 'High Score' -- Never reached
END
-- ✅ Specific before general
CASE WHEN score > 100 THEN 'High Score'
WHEN score > 0 THEN 'Has Score'
END-- ❌ No ELSE: unmatched rows return NULL
CASE WHEN status = 'open' THEN 'Active'
WHEN status = 'closed' THEN 'Resolved'
END -- What about 'cold' status? Returns NULL!
-- ✅ Always include ELSE unless you want NULL
CASE WHEN status = 'open' THEN 'Active'
WHEN status = 'closed' THEN 'Resolved'
ELSE 'Other'
ENDCASE WHEN phone = NULL THEN 'No phone'
ELSE phone
END•= NULL always evaluates to UNKNOWN (not TRUE)
•Every row falls to ELSE
•No rows ever get 'No phone'
CASE WHEN phone IS NULL THEN 'No phone'
ELSE phone
END•IS NULL correctly tests for NULL values
•Rows with missing phone numbers now get 'No phone'
•Standard SQL behavior across all databases
Nothing equals NULL in SQL, not even NULL itself. Always use IS NULL.
| Pattern | Syntax | Use Case |
|---|---|---|
| Categorize | CASE WHEN x > 10 THEN 'high' END | Create labels from values |
| Pivot | COUNT(CASE WHEN status='open' THEN 1 END) | Turn rows into columns |
| Custom sort | ORDER BY CASE WHEN ... END | Non-alphabetical ordering |
| NULL handling | CASE WHEN x IS NULL THEN 'N/A' END | Replace missing values |
| Conditional SUM | SUM(CASE WHEN type='A' THEN amount END) | Sum specific subsets |
All CASE WHEN patterns in one place
Q1.What happens if no WHEN condition matches and there's no ELSE clause?
Q2.Which CASE form lets you use range comparisons like > or BETWEEN?
Q3.You want to count how many orders are 'high value' (over $1000). Which pattern works?
Q4.Why does CASE WHEN phone = NULL THEN 'missing' never work?
Ready to put CASE WHEN into practice?
CASE WHEN is one of the most-used SQL patterns in real work and interviews. Build muscle memory by solving detective cases where you classify evidence and crack mysteries with conditional logic.
Start Your Investigation →CASE is standard SQL and works in every database. IF is MySQL-specific (SQL Server has IIF). Use CASE for portability across databases.
Yes, but it's rarely needed. Most CASE-in-WHERE can be rewritten as simple boolean conditions, which are clearer and often faster because the optimizer can use indexes.
Minimal impact. CASE is evaluated per-row like any other expression. The bigger performance concern is using CASE in WHERE, which can prevent the database from using indexes effectively.
Yes. Each CASE expression is independent. You can have as many CASE columns as you need in a single SELECT statement.
Using CASE inside aggregate functions (COUNT, SUM) to turn row values into columns. It works in all SQL dialects, unlike the PIVOT keyword which is only available in SQL Server and Oracle.