CASE expressions let you embed if-then-else logic directly inside SQL queries — not just in SELECT, but in WHERE filters, GROUP BY buckets, and aggregate functions. This lesson teaches every form with production-realistic examples so you can write smarter queries without touching application code.
You're pulling a sales report and your manager wants orders flagged as "Small," "Medium," or "Large" based on total value. You could export to Excel and use IF statements. You could write three separate queries and stitch them together. Or you could do it in one clean SQL query using a CASE expression — and have the answer in seconds.
CASE expressions are one of SQL's most underutilized tools. Most people learn the basic syntax early and then stop there, not realizing that CASE works almost anywhere in a query: inside SELECT to transform output, inside WHERE to build dynamic filter conditions, inside GROUP BY to bucket rows on the fly, and inside aggregate functions to build conditional summaries. Mastering CASE expressions means you can replace a surprising amount of post-processing logic with pure SQL — and that's the difference between a query that hands someone a dataset and a query that hands someone an answer.
By the end of this lesson, you'll write CASE expressions with confidence, know which form to reach for in which situation, and understand the patterns that show up repeatedly in real production queries.
What you'll learn:
You should be comfortable writing basic SELECT queries with WHERE and GROUP BY clauses. If you need a refresher, SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries covers the foundation. You should also understand how NULL behaves in SQL — if that's fuzzy, read NULL Handling in SQL: IS NULL, COALESCE, and NULLIF before continuing, because NULL interacts with CASE in ways that will catch you off guard.
SQL gives you two syntactic forms: searched CASE and simple CASE. They're related but distinct, and choosing the right one makes your intent clearer to anyone reading your query.
The searched form evaluates a separate Boolean condition in each WHEN clause:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
This is the more powerful and flexible form. Each condition can reference any column, use any comparison operator, combine multiple columns with AND/OR, or even invoke a subquery. The database evaluates conditions from top to bottom and returns the result for the first one that evaluates to TRUE. If no condition matches and you have an ELSE clause, that value is returned. If no condition matches and there's no ELSE, you get NULL.
The simple form compares a single expression against a list of values:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE default_result
END
This is equivalent to a series of expression = valueN comparisons. It's more concise when you're checking one column against a fixed list of values, but it can't express range conditions or multi-column logic.
Here's both forms solving the same problem — translating a status code to a human-readable label:
-- Simple CASE: clean and direct when matching exact values
SELECT
order_id,
CASE status_code
WHEN 1 THEN 'Pending'
WHEN 2 THEN 'Processing'
WHEN 3 THEN 'Shipped'
WHEN 4 THEN 'Delivered'
ELSE 'Unknown'
END AS order_status
FROM orders;
-- Searched CASE: same result, but necessary when conditions get more complex
SELECT
order_id,
CASE
WHEN status_code = 1 THEN 'Pending'
WHEN status_code = 2 THEN 'Processing'
WHEN status_code = 3 THEN 'Shipped'
WHEN status_code = 4 THEN 'Delivered'
ELSE 'Unknown'
END AS order_status
FROM orders;
In practice, most experienced SQL writers default to the searched form because it's easier to extend and doesn't require a mental switch between forms when requirements change. Pick the simple form deliberately when clarity benefits from it — matching a status code or category column against a short, fixed list is a good case.
Tip
CASE expressions always return a single value of a consistent data type. The database will attempt to implicitly cast THEN results to a common type, but mixing strings and numbers across THEN branches will cause errors or unexpected implicit conversions. Keep your result values type-consistent.
This is where most people start with CASE, and for good reason — it's immediately useful. You're essentially adding a computed column to your result set based on conditions applied to existing columns.
Imagine you're working with an e-commerce database and need to segment orders by size for a reporting dashboard:
SELECT
order_id,
customer_id,
order_total,
CASE
WHEN order_total < 50.00 THEN 'Small'
WHEN order_total < 200.00 THEN 'Medium'
WHEN order_total < 500.00 THEN 'Large'
ELSE 'Enterprise'
END AS order_tier,
order_date
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_total DESC;
Notice the range logic here: because CASE short-circuits on the first matching condition, you don't need to write WHEN order_total >= 50.00 AND order_total < 200.00 for the second branch. If you've already passed the first check, you know the value is at least 50.00. This keeps conditions clean — but it also means order matters. Flip those conditions and you'll get wrong results silently:
-- BUG: Every order under 500 will match 'Small' because it's checked first
CASE
WHEN order_total < 500.00 THEN 'Small' -- wrong: catches Medium and Large too
WHEN order_total < 200.00 THEN 'Medium' -- never reached for orders under 500
WHEN order_total < 50.00 THEN 'Large' -- never reached
ELSE 'Enterprise'
END
This is one of the most common CASE mistakes, and it produces no error — just wrong answers. Always order your range conditions from most specific to most general, or explicitly spell out the full range in each condition if you want the logic to be self-documenting.
Searched CASE lets you build conditions that cross multiple columns. Suppose you're analyzing customer churn risk and want to classify accounts by both their spending tier and their days since last purchase:
SELECT
customer_id,
customer_name,
total_lifetime_value,
days_since_last_order,
CASE
WHEN total_lifetime_value >= 10000 AND days_since_last_order <= 90 THEN 'VIP Active'
WHEN total_lifetime_value >= 10000 AND days_since_last_order > 90 THEN 'VIP At Risk'
WHEN total_lifetime_value >= 1000 AND days_since_last_order <= 90 THEN 'Regular Active'
WHEN total_lifetime_value >= 1000 AND days_since_last_order > 90 THEN 'Regular At Risk'
WHEN days_since_last_order > 365 THEN 'Lapsed'
ELSE 'New'
END AS customer_segment
FROM customers;
This is significantly more expressive than anything you'd build in a WHERE clause. You're producing a new analytical column at query time, without touching your schema.
Here's where NULL bites people. NULL doesn't equal anything — not even NULL. So if a column can be NULL and you want to handle that case, you need an explicit IS NULL check:
SELECT
product_id,
product_name,
discontinued_date,
CASE
WHEN discontinued_date IS NULL THEN 'Active'
WHEN discontinued_date > CURRENT_DATE THEN 'Scheduled for Discontinuation'
ELSE 'Discontinued'
END AS product_status
FROM products;
If you wrote WHEN discontinued_date = NULL THEN 'Active', it would never match. That condition always evaluates to NULL (which is falsy), and your NULLs would fall through to the ELSE clause or return NULL if there's no ELSE. This trips up even experienced practitioners.
Warning
A CASE expression with no ELSE clause returns NULL when no WHEN condition matches. This is rarely what you want in a SELECT column, since NULL in output is ambiguous and often causes downstream issues. Always include an ELSE unless you explicitly want unmatched rows to produce NULL.
Using CASE in WHERE clauses is less common, but it solves a specific and genuinely tricky problem: applying different filter logic depending on context, within a single query.
The classic use case is a reporting query where you want to optionally filter based on a user-selected parameter. In application code, you might dynamically build SQL strings — but that approach has security implications and maintenance headaches. CASE inside WHERE gives you a cleaner alternative.
Suppose you have a stored procedure or parameterized query that accepts a filter_type argument:
-- Return rows where the condition appropriate to filter_type is satisfied
SELECT
order_id,
customer_id,
order_total,
order_status
FROM orders
WHERE
CASE :filter_type
WHEN 'high_value' THEN CASE WHEN order_total >= 500 THEN 1 ELSE 0 END
WHEN 'recent' THEN CASE WHEN order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 1 ELSE 0 END
WHEN 'pending' THEN CASE WHEN order_status = 'Pending' THEN 1 ELSE 0 END
ELSE 1 -- no filter: return everything
END = 1;
This is a nested CASE pattern. The outer CASE selects which inner CASE to evaluate based on the parameter, and the whole expression resolves to 1 (include) or 0 (exclude). It's a bit unusual-looking, but it's valid SQL and it consolidates what would otherwise be multiple queries or complex OR logic into one readable statement.
Note
While this pattern works, be aware that databases can struggle to optimize CASE inside WHERE because the filter condition is opaque to the query planner. If performance matters, consider Advanced SQL Filtering and Sorting: Mastering WHERE, ORDER BY, and Query Optimization for alternatives like conditional JOINs or dynamic SQL approaches. For smaller datasets, the readability benefit usually outweighs any optimization concerns.
Another practical WHERE use: filtering based on relationships between columns in the same row, where the logic depends on other column values:
-- Include orders where the approval threshold depends on the order type
SELECT
order_id,
order_type,
order_total,
requires_approval
FROM orders
WHERE
CASE order_type
WHEN 'standard' THEN CASE WHEN order_total > 1000 THEN 1 ELSE 0 END
WHEN 'wholesale' THEN CASE WHEN order_total > 5000 THEN 1 ELSE 0 END
WHEN 'internal' THEN CASE WHEN order_total > 500 THEN 1 ELSE 0 END
ELSE 0
END = 1
AND requires_approval = TRUE;
This returns orders that exceed their type-specific approval threshold. Without CASE, you'd need a relatively unwieldy OR chain:
-- Equivalent without CASE — harder to read and maintain
WHERE (
(order_type = 'standard' AND order_total > 1000) OR
(order_type = 'wholesale' AND order_total > 5000) OR
(order_type = 'internal' AND order_total > 500)
)
AND requires_approval = TRUE;
The OR version is actually fine here and arguably more transparent to the query optimizer. The CASE version becomes genuinely superior when conditions get more complex — multiple comparisons per branch, computed conditions, or when you're pulling the logic from a configuration table.
This is one of CASE's most powerful and underused applications. Instead of grouping by a column that already exists in your schema, you group by a CASE expression — effectively creating a custom bucketing scheme on the fly.
Continuing the orders example, let's say you want a count and revenue total by order tier, not by any existing column:
SELECT
CASE
WHEN order_total < 50.00 THEN 'Small'
WHEN order_total < 200.00 THEN 'Medium'
WHEN order_total < 500.00 THEN 'Large'
ELSE 'Enterprise'
END AS order_tier,
COUNT(*) AS order_count,
SUM(order_total) AS total_revenue,
AVG(order_total) AS avg_order_value
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY
CASE
WHEN order_total < 50.00 THEN 'Small'
WHEN order_total < 200.00 THEN 'Medium'
WHEN order_total < 500.00 THEN 'Large'
ELSE 'Enterprise'
END
ORDER BY MIN(order_total); -- natural sort order for the tiers
You write the CASE expression twice — once in SELECT and once in GROUP BY. This repetition is unavoidable in standard SQL (most databases don't allow you to reference SELECT aliases in GROUP BY). Some databases like BigQuery allow you to use the alias or the column position number, but for portability, always repeat the full expression.
Tip
In PostgreSQL and some other databases, you can GROUP BY the ordinal position of a SELECT column: GROUP BY 1 would reference the first column. This avoids repeating the CASE expression, but it makes queries harder to read and is fragile when column order changes. Use it judiciously.
You can group by multiple CASE expressions simultaneously. Here's a query that cross-tabulates customers by both value tier and activity status — producing a 2D segment matrix in pure SQL:
SELECT
CASE
WHEN total_lifetime_value >= 10000 THEN 'High Value'
WHEN total_lifetime_value >= 1000 THEN 'Mid Value'
ELSE 'Low Value'
END AS value_tier,
CASE
WHEN days_since_last_order <= 30 THEN 'Active'
WHEN days_since_last_order <= 90 THEN 'Cooling'
ELSE 'Dormant'
END AS activity_status,
COUNT(*) AS customer_count,
AVG(total_lifetime_value) AS avg_ltv,
AVG(days_since_last_order) AS avg_days_inactive
FROM customers
GROUP BY
CASE
WHEN total_lifetime_value >= 10000 THEN 'High Value'
WHEN total_lifetime_value >= 1000 THEN 'Mid Value'
ELSE 'Low Value'
END,
CASE
WHEN days_since_last_order <= 30 THEN 'Active'
WHEN days_since_last_order <= 90 THEN 'Cooling'
ELSE 'Dormant'
END
ORDER BY
avg_ltv DESC,
avg_days_inactive ASC;
This kind of query replaces what a pivot table in Excel might do, entirely within SQL. For a more comprehensive look at SQL aggregation patterns, Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG goes deep on grouping mechanics.
This is arguably the most powerful pattern: embedding CASE expressions inside aggregate functions like SUM, COUNT, and AVG. The result is conditional aggregation — computing multiple different summaries in a single pass through the data, without multiple queries or subqueries.
The pattern is: SUM(CASE WHEN condition THEN 1 ELSE 0 END) for counts, and SUM(CASE WHEN condition THEN value ELSE 0 END) for sums. The CASE expression either includes or excludes each row from the aggregation.
Here's a practical example: a monthly order summary that breaks revenue down by order tier, all in one query:
SELECT
DATE_TRUNC('month', order_date) AS order_month,
COUNT(*) AS total_orders,
SUM(order_total) AS total_revenue,
-- Conditional counts by tier
SUM(CASE WHEN order_total < 50 THEN 1 ELSE 0 END) AS small_order_count,
SUM(CASE WHEN order_total < 200
AND order_total >= 50 THEN 1 ELSE 0 END) AS medium_order_count,
SUM(CASE WHEN order_total >= 200 THEN 1 ELSE 0 END) AS large_order_count,
-- Conditional revenue by tier
SUM(CASE WHEN order_total < 50 THEN order_total ELSE 0 END) AS small_order_revenue,
SUM(CASE WHEN order_total < 200
AND order_total >= 50 THEN order_total ELSE 0 END) AS medium_order_revenue,
SUM(CASE WHEN order_total >= 200 THEN order_total ELSE 0 END) AS large_order_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;
Notice that inside aggregate functions, you generally need to be explicit about ranges (I wrote < 200 AND >= 50 rather than relying on short-circuit ordering). The short-circuit behavior applies to evaluation within a single CASE expression, but here you have separate CASE expressions — one per column — and they evaluate independently for each row.
This pattern of using CASE inside aggregates is so common and powerful that it has its own article: Conditional Aggregation with CASE WHEN: Pivoting Logic Without Reshaping Your Data covers advanced applications including pivoting row data into columns.
Key insight
SUM(CASE WHEN condition THEN 1 ELSE 0 END) is functionally equivalent to COUNT(CASE WHEN condition THEN 1 ELSE NULL END). Both count the rows where the condition is true. The SUM version is slightly more intuitive to read; the COUNT version exploits the fact that COUNT ignores NULLs. Either is correct, but pick one and be consistent within a codebase.
Averaging only a subset of rows requires more care. You can't just put 0 in the ELSE branch, because 0 would pull the average down. Use NULL instead — aggregate functions skip NULLs:
SELECT
product_category,
AVG(order_total) AS overall_avg_order,
-- Average order value for large orders only (not skewed by including zeros)
AVG(CASE WHEN order_total >= 200 THEN order_total ELSE NULL END) AS large_order_avg,
-- Average order value for new customers only
AVG(CASE WHEN customer_since >= CURRENT_DATE - INTERVAL '90 days'
THEN order_total
ELSE NULL
END) AS new_customer_avg_order
FROM orders
JOIN products USING (product_id)
GROUP BY product_category;
Here, NULL in the ELSE position means "don't include this row in the average calculation." If you put 0, every row would be included — and your average would be dramatically wrong for sparse conditions.
Let's put everything together in a realistic scenario. You're a data analyst at a SaaS company and you need to build a customer health scorecard. For each account, you want to score them on three dimensions (usage, support burden, and contract value), roll those into a composite health grade, and produce a summary by account manager showing how many accounts are at risk.
Here's the full query:
WITH customer_scores AS (
SELECT
c.customer_id,
c.account_manager,
c.contract_value,
c.plan_type,
-- Usage score: based on feature adoption rate
CASE
WHEN u.feature_adoption_pct >= 70 THEN 3
WHEN u.feature_adoption_pct >= 40 THEN 2
WHEN u.feature_adoption_pct >= 10 THEN 1
ELSE 0
END AS usage_score,
-- Support score: penalize for high support volume
CASE
WHEN s.open_tickets = 0 AND s.tickets_last_90d < 3 THEN 3
WHEN s.open_tickets <= 1 AND s.tickets_last_90d < 8 THEN 2
WHEN s.open_tickets <= 3 THEN 1
ELSE 0
END AS support_score,
-- Value score: weight by contract size
CASE
WHEN c.contract_value >= 50000 THEN 3
WHEN c.contract_value >= 10000 THEN 2
WHEN c.contract_value >= 2000 THEN 1
ELSE 0
END AS value_score
FROM customers c
JOIN customer_usage u ON c.customer_id = u.customer_id
JOIN support_summary s ON c.customer_id = s.customer_id
WHERE c.status = 'Active'
),
customer_health AS (
SELECT
customer_id,
account_manager,
contract_value,
plan_type,
usage_score,
support_score,
value_score,
(usage_score + support_score + value_score) AS total_score,
-- Composite health grade based on total score
CASE
WHEN (usage_score + support_score + value_score) >= 8 THEN 'Healthy'
WHEN (usage_score + support_score + value_score) >= 5 THEN 'Needs Attention'
WHEN (usage_score + support_score + value_score) >= 3 THEN 'At Risk'
ELSE 'Critical'
END AS health_grade
FROM customer_scores
)
-- Summary by account manager: how is their book performing?
SELECT
account_manager,
COUNT(*) AS total_accounts,
SUM(contract_value) AS total_arr,
-- Count by health grade
SUM(CASE WHEN health_grade = 'Healthy' THEN 1 ELSE 0 END) AS healthy_count,
SUM(CASE WHEN health_grade = 'Needs Attention' THEN 1 ELSE 0 END) AS needs_attention_count,
SUM(CASE WHEN health_grade = 'At Risk' THEN 1 ELSE 0 END) AS at_risk_count,
SUM(CASE WHEN health_grade = 'Critical' THEN 1 ELSE 0 END) AS critical_count,
-- Revenue at risk
SUM(CASE WHEN health_grade IN ('At Risk', 'Critical') THEN contract_value ELSE 0 END) AS arr_at_risk,
-- At-risk percentage of total ARR
ROUND(
100.0 * SUM(CASE WHEN health_grade IN ('At Risk', 'Critical') THEN contract_value ELSE 0 END)
/ NULLIF(SUM(contract_value), 0),
1
) AS pct_arr_at_risk
FROM customer_health
GROUP BY account_manager
ORDER BY arr_at_risk DESC;
This query demonstrates CASE at every level: inside CTEs to derive scores, in a second CTE to create the health grade, and then inside aggregate functions in the final SELECT to produce the per-manager summary. It's structured using CTEs to keep each layer readable — if CTEs are new to you, Common Table Expressions (CTEs) for Cleaner SQL is an excellent follow-on read.
Note
The NULLIF(SUM(contract_value), 0) in the percentage calculation prevents a division-by-zero error if an account manager has $0 total ARR. This is a common defensive pattern worth building into any ratio calculation.
Work through these progressively. Use your own database or set up a sample schema with orders (order_id, customer_id, order_total, order_date, order_status, region) and customers (customer_id, customer_name, signup_date, plan_type).
Exercise 1 — Basic classification:
Write a SELECT query that returns each order with a fulfillment_priority column: 'Urgent' for orders over $1,000, 'Normal' for orders between $100 and $1,000, 'Low' for orders under $100. Include an ELSE that handles any NULL order_total values by returning 'Unknown'.
Exercise 2 — GROUP BY bucketing:
Modify your query from Exercise 1 to count orders and sum revenue by fulfillment_priority, without using any other GROUP BY columns. Sort results so 'Urgent' appears first.
Exercise 3 — Conditional aggregation: Write a single query that produces one row per region, with separate columns for the count of orders in each status (Pending, Processing, Shipped, Delivered) and the total revenue for each status. Do this without subqueries or multiple queries — only CASE inside aggregates.
Exercise 4 — Multi-condition classification:
Join orders to customers and classify each order as 'New Customer Large Order' (customer signed up within 90 days AND order_total >= 500), 'New Customer Small Order' (customer signed up within 90 days AND order_total < 500), 'Returning Customer' (customer older than 90 days), or 'Unknown' if customer data is missing. Count orders by classification.
Exercise 5 — Challenge: Reproduce Exercise 3 but add a column showing what percentage of each region's total revenue comes from 'Delivered' orders. Handle the division carefully to avoid divide-by-zero errors.
As covered earlier, CASE short-circuits. If your ranges overlap because conditions aren't ordered from most specific to most general, rows will match the wrong branch. Always verify your ranges are mutually exclusive and ordered correctly, or make each condition fully explicit.
Without ELSE, unmatched rows produce NULL. This is sometimes intentional, but more often it means you forgot to handle an edge case. Check your output for unexpected NULLs in CASE-derived columns.
-- Wrong: pulls average down by including non-matching rows as 0
AVG(CASE WHEN condition THEN value ELSE 0 END)
-- Correct: NULL is ignored by AVG, so only matching rows contribute
AVG(CASE WHEN condition THEN value ELSE NULL END)
-- Or equivalently:
AVG(CASE WHEN condition THEN value END) -- omitting ELSE gives NULL by default
-- Never matches, even when the column IS NULL
CASE WHEN some_column = NULL THEN 'Missing' ...
-- Correct
CASE WHEN some_column IS NULL THEN 'Missing' ...
This is basic NULL semantics, but it appears in CASE bugs constantly. If a column can be NULL, put the IS NULL check first (or early) in your WHEN list.
-- Will error or produce unexpected implicit casts
CASE
WHEN condition THEN 42 -- integer
WHEN other THEN 'unknown' -- string
END
All THEN and ELSE results should resolve to the same (or compatible) data type. If you're mixing numeric results with string labels, cast explicitly: THEN CAST(42 AS VARCHAR).
If you want to sort by a CASE-derived column, some databases let you use the alias, while others require repeating the expression or using the ordinal position. Know your database's rules here.
-- PostgreSQL: alias works in ORDER BY
SELECT
CASE WHEN score >= 8 THEN 'A' ELSE 'B' END AS grade
FROM scores
ORDER BY grade; -- works in PostgreSQL
-- Safer/portable alternative: use the expression directly
ORDER BY CASE WHEN score >= 8 THEN 'A' ELSE 'B' END;
Warning
Performance with CASE expressions inside GROUP BY or WHERE can degrade on large tables because those expressions typically can't use standard indexes. If you find yourself repeatedly grouping or filtering by the same CASE logic, consider materializing the derived column — either as a computed/generated column in your schema or via a materialized view. This is worth investigating when query times become unacceptable.
CASE expressions are SQL's primary tool for conditional logic, and they work across more of a query than most people realize. Here's what we covered:
The real-world project demonstrated that these aren't isolated techniques — they combine naturally into queries that answer genuinely complex business questions without resorting to post-processing in application code or spreadsheets.
Where to go from here:
The most powerful immediate extension of what you've learned is Conditional Aggregation with CASE WHEN: Pivoting Logic Without Reshaping Your Data, which digs deeper into the conditional aggregation pattern and shows how to use it for true SQL pivots. If you're interested in how CASE interacts with window functions — another area where it adds real expressive power — Window Functions: RANK, ROW_NUMBER, and LAG is your next stop. And if you want to push your aggregation skills further, Writing Efficient SQL Aggregations: GROUP BY, HAVING, and Grouping Sets Explained covers advanced grouping patterns that complement everything here.