skip to today's puzzle

CASE turns values into labels

Starting table: menu_items

Starting menu_items data before the example query
nameprice
Espresso3
Mocha5.5
Brunch plate12

CASE is SQL's way of saying if this, then that. Each WHEN holds a test and its THEN holds the label to use when that test passes. SQL reads the WHEN tests from the top down and stops at the first one that is true. ELSE gives the label for rows that passed no test, and END closes the CASE.

A tiny example

In plain English: Show me each menu item, labeled everyday when it costs under 5 and treat otherwise.

SELECT name, price, CASE
  WHEN price < 5 THEN 'everyday'
  ELSE 'treat'
END AS choice
FROM menu_items;

Query result

namepricechoice
Espresso3everyday
Mocha5.5treat
Brunch plate12treat

Espresso costs 3, which is under 5, so it’s labeled everyday. Mocha (5.5) and the brunch plate (12) don't pass that test, so ELSE labels them treat. Best of all, CASE is universal ANSI SQL, so this exact syntax works across SQL Server, Databricks, Oracle, and Postgres.

Related SQL lessons

Ready for a break?

A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.

Try today's free SQL puzzle