CASE Expressions

CASE is SQL's if/else. It picks a value based on conditions, row by row, and can be used anywhere a value can: in the select list, in ORDER BY, inside aggregates and in updates. It's how you turn raw data into labels, buckets and custom sort orders.

Searched CASE

SQL

SELECT name, price,
       CASE
         WHEN price < 20 THEN 'Budget'
         WHEN price < 50 THEN 'Standard'
         ELSE 'Premium'
       END AS price_band
FROM products
ORDER BY price;

Result 12 rows

name	price	price_band
Ceramic Mug	12.5	Budget
Smart Plug	14.99	Budget
Jigsaw Puzzle 1000	18	Budget
USB-C Charger	19.99	Budget
The Patient Gardener	24.9	Standard
Learning SQL Step by Step	32.5	Standard
Desk Lamp	34	Standard
Wooden Train Set	39.9	Standard
Night Sky Atlas	45	Standard
Linen Throw	49	Standard
Wireless Earbuds	59.99	Premium
Mechanical Keyboard	89	Premium

Conditions are checked in order, and the first true one wins — so the second WHEN only sees prices of 20 or more. If nothing matches and there's no ELSE, the result is NULL.

Simple CASE

SQL

SELECT id, status,
       CASE status
         WHEN 'pending' THEN 'Awaiting dispatch'
         WHEN 'shipped' THEN 'On its way'
         WHEN 'delivered' THEN 'Complete'
         ELSE 'Cancelled'
       END AS customer_message
FROM orders
WHERE id IN (5, 9, 12, 14);

Result 4 rows

id	status	customer_message
5	cancelled	Cancelled
9	shipped	On its way
12	pending	Awaiting dispatch
14	delivered	Complete

When every condition compares the same expression for equality, the simple form is shorter. It can't test for NULL (WHEN NULL never matches), so use the searched form for that.

CASE in ORDER BY

SQL

SELECT id, status
FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'shipped' THEN 2
           WHEN 'delivered' THEN 3
           ELSE 4
         END,
         id
LIMIT 7;

Result 7 rows

id	status
12	pending
13	pending
15	pending
9	shipped
11	shipped
1	delivered
2	delivered

A custom order that isn't alphabetical — here, the order a warehouse wants to see orders in.

CASE with NULL checks

SQL

SELECT name,
       CASE
         WHEN email IS NULL THEN 'Missing email'
         WHEN city IS NULL THEN 'Missing city'
         ELSE 'Complete'
       END AS profile
FROM customers
WHERE email IS NULL OR city IS NULL OR id = 1;

Result 3 rows

name	profile
Ana Pereira	Complete
Farah Haddad	Missing city
Julia Santos	Missing email

CASE inside aggregates

SQL

SELECT COUNT(*) AS orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders;

Result 1 row

orders	delivered	pending
15	9	3

Counting several categories in a single pass is a powerful pattern — lesson 21 builds on it.

Shortcut functions

Function	Equivalent to	Where
`COALESCE(a, b)`	`CASE WHEN a IS NOT NULL THEN a ELSE b END`	Everywhere
`NULLIF(a, b)`	`CASE WHEN a = b THEN NULL ELSE a END`	Everywhere
`IIF(cond, a, b)`	`CASE WHEN cond THEN a ELSE b END`	SQLite, SQL Server
`IF(cond, a, b)`	Same	MySQL

SQL

SELECT name, stock, IIF(stock = 0, 'Sold out', 'Available') AS availability
FROM products
WHERE category = 'Electronics';

Result 4 rows

name	stock	availability
Wireless Earbuds	60	Available
USB-C Charger	120	Available
Mechanical Keyboard	0	Sold out
Smart Plug	75	Available

Try it yourself

Label each employee as "Junior" (under 55,000), "Mid" (under 80,000) or "Senior".

PreviousNULL Values NextOperators & Expressions