CASE WHEN

Add if-then-else logic directly inside your queries

Intermediate 14 minCASEconditionallogic

Conditional Expressions

CASE WHEN is SQL's if-then-else. It creates new computed values based on conditions, right inside your query — no need to modify the underlying data. Here's how it works:

Top-to-bottom evaluation — Conditions are checked in order. The first match wins and all remaining WHENs are skipped entirely. Structure: CASE → WHEN condition THEN result → ELSE default → END.

Always include ELSE — Without an ELSE clause, rows that don't match any WHEN condition return NULL. This can silently break downstream calculations, aggregates, and reports without any visible error.

Works inside aggregates — SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END) creates conditional totals in a single query. This is a powerful pivot-style pattern for building reports without restructuring your data.

WHEN order is critical — WHEN total >= 50 before WHEN total > 200 means a 500 order matches the first condition and gets mislabeled. Always put the most restrictive condition first to ensure correct categorization.

Knowledge Canvas

How CASE Works

SQL's if-then-else

CASE evaluates conditions top to bottom — first match wins
ELSE catches everything not matched above
Without ELSE, unmatched rows get NULL
Can be used in SELECT, WHERE, ORDER BY, GROUP BY

CASE Patterns

Powerful categorization techniques

Bucketing: CASE WHEN price < 50 THEN 'Budget' WHEN price < 200 THEN 'Mid' ELSE 'Premium' END
Conditional aggregation: SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)
Dynamic sorting: ORDER BY CASE WHEN priority = 'high' THEN 1 ... END
Pivoting rows to columns with CASE inside aggregates

CASE Gotchas

Watch the order

First match wins — order conditions from specific to general
Forgetting ELSE → NULL for unmatched rows
CASE cannot be used for control flow (no GOTO, no loops)
Nested CASE is possible but hard to read — prefer COALESCE

When to Use CASE

Classification and transformation

Label numeric codes: 1→Active, 2→Paused, 3→Cancelled
Create age/price/date buckets for reporting
Conditional aggregation in pivot queries
Custom sort orders not based on column values

Syntax

Syntax Template

1CASE

2 WHEN condition1 THEN result1

3 WHEN condition2 THEN result2

4 ELSE default_result

5END AS alias

WHEN ... THENIf condition is true, return this result

ELSEDefault if no conditions match (returns NULL if omitted)

ENDRequired — closes the CASE expression

AS aliasName the computed column

Sample Data

orders

12 rows

id	total	status
5001	59.98	delivered
5002	449	delivered
5003	12.5	shipped
5004	89.99	delivered
5005	245	pending
5006	159	delivered
5007	45.5	shipped
5008	79	cancelled
5009	425	delivered
5010	18.99	pending
5011	134	delivered
5012	65.5	cancelled

products

10 rows

id	name	category	price	stock
P101	Wireless Mouse	Electronics	24.99	150
P102	Office Desk	Furniture	189	25
P103	LED Desk Lamp	Furniture	45.5	80
P104	USB-C Cable	Electronics	12.99	500
P105	Bluetooth Speaker	Electronics	79	60
P106	Office Chair	Furniture	95	15
P107	Coffee Maker	Appliances	89.99	40
P108	Notebook Set	Stationery	8.5	200
P109	Wireless Headphones	Electronics	159	30
P110	Standing Desk	Furniture	425	8

Worked Example

Categorize orders as "High" (above 200), "Medium" (50-200), or "Low" (below 50).

SQL

1SELECT

2 id,

3 total,

4 CASE

5 WHEN total > 200 THEN 'High'

6 WHEN total >= 50 THEN 'Medium'

7 ELSE 'Low'

8 END AS value_tier

9FROM orders

10ORDER BY

11 value_tier,

12 total DESC

13LIMIT 6;

For each row, CASE evaluates conditions top to bottom. Order #5007 (748) hits total > 200 first → "High". An order of 59.98 fails the first WHEN but hits total >= 50 → "Medium". An order of 12.50 fails both → ELSE → "Low". The order of WHEN clauses matters! We ORDER BY value_tier then total DESC so the preview shows a sample from each tier.

Output

6 rows

id	total	value_tier
5002	449	High
5009	425	High
5005	245	High
5007	45.5	Low
5010	18.99	Low
5003	12.5	Low

Common Mistakes

❌

WHEN order matters

SQL

1SELECT

2 id,

3 total,

4 CASE

5 WHEN total >= 50 THEN 'Medium'

6 WHEN total > 200 THEN 'High'

7 ELSE 'Low'

8 END AS value_tier

9FROM orders

10ORDER BY total DESC

11LIMIT 3;

A 449 order matches total >= 50 first, so it's labeled 'Medium' instead of 'High'. Run it — every order ≥ 200 also gets 'Medium' because the second WHEN is never reached.

Put the most restrictive condition first: check > 200 before >= 50.

❌

Forgetting END

CASE without END causes a syntax error. Every CASE must be closed with END.

Always write END, optionally followed by AS alias_name.

Key Concepts

1First matching WHEN wins — put the most specific condition first

2CASE without ELSE returns NULL for unmatched rows

3CASE works inside SUM, COUNT, and other aggregates

4Use CASE in ORDER BY for custom sort logic

Pro Tip

Raw data rarely has the categories you need for analysis. CASE WHEN transforms and categorizes data on the fly.

When to Use

Labeling orders as high/medium/low, customer segments, price buckets, conditional aggregates, pivot-style reports.

Challenge

Solve the problem below

Show product name, price, and a column called 'price_range' that says 'Budget' for under 25, 'Mid-range' for 25-100, and 'Premium' for over 100.

Your Query

LIKE & Pattern Matching

COUNT Function