AI Hints
Homeโ€บSQL Tutorialโ€บCalculations and Labels
SQL ยท Lesson 22 of 126

Labels with CASE

If this, then that.

โฑ 2 min readโ–ถ Video at 30:39๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Labels with CASE
From the video: Labels 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.

query.sqlsql
SELECT title, price,
       CASE
         WHEN price < 12 THEN 'Budget'
         WHEN price < 20 THEN 'Standard'
         ELSE 'Premium'
       END AS band
FROM books
LIMIT 6;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        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.

CASEstart the rule
WHEN ... THENtest, and result
ELSEeverything else
ENDfinish the rule

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
Next up: Practice: Prices and Labels โ€” put it together