SQL Tutorial › Querying Data › CASE Expression

SQL CASE Expression

Q: How do you write IF-THEN-ELSE in SQL?

Use CASE WHEN condition THEN value ELSE other_value END. MySQL also has IF(condition, value, other_value) for a single test.

Q: What is the difference between simple CASE and searched CASE?

Simple CASE x WHEN 1 THEN ... compares one expression with values using equality. Searched CASE WHEN condition THEN ... can test any condition, including ranges and IS NULL.

Q: Can I use CASE in a WHERE clause?

Yes, because CASE returns a value, but a plain combination of AND and OR is usually clearer and can use indexes better.

Q: How do I pivot rows to columns in MySQL?

Group the rows and use one SUM(CASE WHEN ... THEN 1 ELSE 0 END) (or MAX(CASE ...)) column per value you want as a column.

IntermediateLesson 17 of 6912 min read7 queries run on MySQL 8.4

CASE is SQL’s if-then-else. It looks at each row, checks conditions in order, and returns the value of the first one that is true. Use it to label rows (“High”, “Low”), to sort in a custom order, and, combined with SUM or COUNT, to turn rows into report columns.

In this lesson

Write searched CASE WHEN ... THEN ... ELSE ... END
Use the short simple CASE form
Sort with a custom order
Count and pivot with SUM(CASE ...)

CASE syntax

MySQL

CASE  WHEN condition1 THEN result1  WHEN condition2 THEN result2  ...  ELSE default_resultEND

Conditions are checked top to bottom; the first true one wins.
If none is true and there is no ELSE, the result is NULL.
CASE is an expression, so it can be used anywhere a value can: SELECT, WHERE, ORDER BY, GROUP BY, inside functions.

Sample data

employees · 12 rows (some columns)

emp_id	first_name	dept_id	salary	commission
100	Meera	NULL	250000	NULL
101	Asha	1	125000	NULL
102	Rahul	1	98000	NULL
103	Priya	1	72000	NULL
104	Karan	1	72000	NULL
105	Vikram	2	105000	12000
106	Neha	2	54000	6000
107	Arjun	2	48000	4500
108	Kavya	3	88000	NULL
109	Rohan	3	45000	NULL
110	Sneha	4	78000	NULL
111	Amit	1	25000	NULL

Label rows: salary bands

SQL

SELECT first_name, salary,       CASE         WHEN salary >= 100000 THEN 'High'         WHEN salary >= 60000  THEN 'Medium'         ELSE 'Entry'       END AS salary_bandFROM employeesORDER BY salary DESC;

ResultMySQL 8.4

first_name	salary	salary_band
Meera	250000	High
Asha	125000	High
Vikram	105000	High
Rahul	98000	Medium
Kavya	88000	Medium
Sneha	78000	Medium
Priya	72000	Medium
Karan	72000	Medium
Neha	54000	Entry
Arjun	48000	Entry
Rohan	45000	Entry
Amit	25000	Entry

12 rows in set

How this query works, step by step

For each row, MySQL tests salary >= 100000 first. Meera, Asha and Vikram get 'High'.
Rows that failed the first test are checked against salary >= 60000: these get 'Medium'. A salary of 125,000 never reaches this test, so order matters.
Everything else falls through to ELSE 'Entry'.
AS salary_band names the new column.

Watch out

Put the most specific condition first. If WHEN salary >= 60000 came first, every high earner would be labelled ‘Medium’.

Simple CASE

When every test compares the same expression for equality, the short form is easier to read:

SQL

SELECT order_id, status,       CASE status         WHEN 'Delivered' THEN 'Closed'         WHEN 'Cancelled' THEN 'Closed'         ELSE 'Open'       END AS ticket_stateFROM orders;

ResultMySQL 8.4

order_id	status	ticket_state
1001	Delivered	Closed
1002	Delivered	Closed
1003	Delivered	Closed
1004	Cancelled	Closed
1005	Delivered	Closed
1006	Shipped	Open
1007	Delivered	Closed
1008	Pending	Open
1009	Delivered	Closed
1010	Delivered	Closed

10 rows in set

How this query works, step by step

CASE status names the expression once.
Each WHEN 'value' is compared with status using =.
Pending and Shipped orders fall through to 'Open'.

Note

The simple form cannot test NULL (CASE x WHEN NULL is never true) or ranges. Use the searched form for those.

CASE with NULL

SQL

SELECT first_name,       CASE         WHEN commission IS NULL THEN 'No commission'         WHEN commission >= 10000 THEN 'Top seller'         ELSE 'Seller'       END AS sales_roleFROM employeesWHERE dept_id IN (2, 3);

ResultMySQL 8.4

first_name	sales_role
Vikram	Top seller
Neha	Seller
Arjun	Seller
Kavya	No commission
Rohan	No commission

5 rows in set

CASE in ORDER BY

Sort products so that out-of-stock items come last, and the rest by price:

SQL

SELECT product_name, stock, priceFROM productsORDER BY CASE WHEN stock = 0 THEN 1 ELSE 0 END, price;

ResultMySQL 8.4

product_name	stock	price
Gel Pens	250	199
Notebook Pack	300	249
Wireless Mouse	120	799
Desk Lamp	30	1499
Monitor Arm	18	2799
Mechanical Keyboard	45	3499
Office Chair	12	8999
Standing Desk	5	18999
USB-C Hub	0	1999

9 rows in set

How this query works, step by step

The CASE gives 0 to products in stock and 1 to out-of-stock products.
Sorting by it first moves the USB-C Hub to the end.
price orders the rest from cheapest to dearest.

Conditional counts with SUM(CASE …)

How many orders does each customer have, and how many of them were delivered?

SQL

SELECT customer_id,       COUNT(*) AS orders,       SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,       SUM(CASE WHEN status <> 'Delivered' THEN 1 ELSE 0 END) AS not_deliveredFROM ordersGROUP BY customer_idORDER BY customer_id;

ResultMySQL 8.4

customer_id	orders	delivered	not_delivered
1	3	2	1
2	2	2	0
3	2	1	1
4	1	1	0
5	2	1	1

5 rows in set

How this query works, step by step

GROUP BY customer_id makes one group per customer.
Inside each group, the CASE turns every delivered order into 1 and every other order into 0.
SUM of those ones and zeros is the number of delivered orders.
This pattern, conditional aggregation, is one of the most useful tricks in SQL reports.

Pivot rows into columns

Headcount per department, with one column per city group:

SQL

SELECT d.dept_name,       SUM(CASE WHEN e.city = 'Bengaluru' THEN 1 ELSE 0 END) AS bengaluru,       SUM(CASE WHEN e.city = 'Pune'      THEN 1 ELSE 0 END) AS pune,       SUM(CASE WHEN e.city NOT IN ('Bengaluru', 'Pune') THEN 1 ELSE 0 END) AS otherFROM departments dJOIN employees e ON e.dept_id = d.dept_idGROUP BY d.dept_nameORDER BY d.dept_name;

ResultMySQL 8.4

dept_name	bengaluru	pune	other
Engineering	4	1	0
HR	0	0	1
Marketing	0	2	0
Sales	0	0	3

4 rows in set

How this query works, step by step

The join gives each employee their department name.
Each SUM(CASE ...) counts the employees of one city group.
The result has one row per department and one column per city group: a pivot table.

IF() in MySQL

For a single condition MySQL also offers IF(condition, then, else):

SQL

SELECT product_name, IF(stock > 0, 'In stock', 'Sold out') AS availabilityFROM products;

ResultMySQL 8.4

product_name	availability
Wireless Mouse	In stock
Mechanical Keyboard	In stock
USB-C Hub	Sold out
Office Chair	In stock
Standing Desk	In stock
Notebook Pack	In stock
Gel Pens	In stock
Desk Lamp	In stock
Monitor Arm	In stock

9 rows in set

IF() is MySQL-only. CASE works in every database, so prefer it in code that might move.

Exercise: Price labels

Label each product as 'Budget' (price under 1,000), 'Standard' (1,000 to 4,999) or 'Premium' (5,000 and above). Show product_name, price and the label as price_label, cheapest first.

Expected resultMySQL 8.4

product_name	price	price_label
Gel Pens	199	Budget
Notebook Pack	249	Budget
Wireless Mouse	799	Budget
Desk Lamp	1499	Standard
USB-C Hub	1999	Standard
Monitor Arm	2799	Standard
Mechanical Keyboard	3499	Standard
Office Chair	8999	Premium
Standing Desk	18999	Premium

9 rows in set

Hint 1

Check the cheapest condition first, then the next, then ELSE.

SQL

SELECT product_name, price,       CASE         WHEN price < 1000 THEN 'Budget'         WHEN price < 5000 THEN 'Standard'         ELSE 'Premium'       END AS price_labelFROM productsORDER BY price;

ResultMySQL 8.4

product_name	price	price_label
Gel Pens	199	Budget
Notebook Pack	249	Budget
Wireless Mouse	799	Budget
Desk Lamp	1499	Standard
USB-C Hub	1999	Standard
Monitor Arm	2799	Standard
Mechanical Keyboard	3499	Standard
Office Chair	8999	Premium
Standing Desk	18999	Premium

9 rows in set

Exercise: Orders by status

In one row, count the delivered, cancelled and other orders.

Expected resultMySQL 8.4

delivered	cancelled	other
7	1	2

1 row in set

Hint 1

Three SUM(CASE ...) columns and no GROUP BY.

SQL

SELECT SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,       SUM(CASE WHEN status NOT IN ('Delivered', 'Cancelled') THEN 1 ELSE 0 END) AS otherFROM orders;

ResultMySQL 8.4

delivered	cancelled	other
7	1	2

1 row in set

Quick quiz

1. A CASE has no ELSE and no condition matches. What is returned?

Without ELSE, the result is NULL.

2. Two WHEN conditions are both true for a row. Which result is used?

CASE stops at the first true condition.

3. What does SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) calculate?

4. Can CASE be used in ORDER BY?

CASE is an expression and works wherever a value is allowed.

CASE statement FAQ

How do you write IF-THEN-ELSE in SQL?

Use CASE WHEN condition THEN value ELSE other_value END. MySQL also has IF(condition, value, other_value) for a single test.

What is the difference between simple CASE and searched CASE?

Simple CASE x WHEN 1 THEN ... compares one expression with values using equality. Searched CASE WHEN condition THEN ... can test any condition, including ranges and IS NULL.

Can I use CASE in a WHERE clause?

Yes, because CASE returns a value, but a plain combination of AND and OR is usually clearer and can use indexes better.

How do I pivot rows to columns in MySQL?

Group the rows and use one SUM(CASE WHEN ... THEN 1 ELSE 0 END) (or MAX(CASE ...)) column per value you want as a column.