Labels with CASE
If this, then that.
Helen wants to label every book as Budget, Standard, or Premium, depending on the price. That's an if this, then that rule. In SQL, rules like that are written with case.

- Budget, Standard, or Premium
- An if-then rule
- Written with CASE
CASE WHEN
Case starts the rule. Each when has a test, and then says what to show if it's true. SQL checks them from top to bottom, and stops at the first one that's true. Else catches everything left over. And end finishes the rule. We name the new column band. Run it. Every book now has a friendly label.
SELECT title, price,
CASE
WHEN price < 12 THEN 'Budget'
WHEN price < 20 THEN 'Standard'
ELSE 'Premium'
END AS band
FROM books
LIMIT 6;โโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโ โ title โ price โ band โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโค โ The Silent Orchard โ 14.99 โ Standard โ โ Code of the River โ 12.99 โ Standard โ โ Stars Over Lagos โ 9.99 โ Budget โ โ A Short History of Bread โ 19.99 โ Standard โ โ Python for Curious Minds โ 34.99 โ Premium โ โ The Last Lighthouse โ 13.99 โ Standard โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโ
Stock levels
Here's another one Helen loves. Sold out when the stock is zero, low when it's under ten, and OK otherwise. Sorting by stock puts the problems at the top. Run it. One book is sold out, and four more are running low.
SELECT title, stock,
CASE
WHEN stock = 0 THEN 'Sold out'
WHEN stock < 10 THEN 'Low'
ELSE 'OK'
END AS level
FROM books
ORDER BY stock
LIMIT 5;โโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโ โ title โ stock โ level โ โโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโค โ The Quiet Kitchen โ 0 โ Sold out โ โ Spice Roads โ 2 โ Low โ โ Data for Everyone โ 3 โ Low โ โ The Last Lighthouse โ 5 โ Low โ โ The Painted Door โ 8 โ Low โ โโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโ
How to read CASE
Read case like a short story. Case, when this is true, then show that. When this other thing is true, then show something else. Else, show this. End. The order matters, because the first true when wins. So put the most specific tests first.
Summary
Case turns rules into labels. It's one of the most useful tools in SQL, and you'll use it again with counting and grouping later.
Key takeaways- CASE WHEN ... THEN ... END
- First true WHEN wins
- ELSE catches the rest