Home / SQL / Reference / Numbers and NULL

SQL CASE

If/else logic that returns a value.

Syntax

CASE
  WHEN condition THEN result
  …
  ELSE default
END

Conditions are checked from the top and the first true one wins. Without ELSE the result is NULL. Works anywhere a value can go, including inside SUM.

Example

Query (on the shop database used in the SQL course)

SELECT name, price,
  CASE WHEN price >= 300 THEN 'premium'
       WHEN price >= 50 THEN 'mid-range'
       ELSE 'budget' END AS band
FROM products
ORDER BY price DESC
LIMIT 6;

Result

namepriceband
Standing desk549.0premium
Ergonomic chair399.0premium
27-inch monitor329.0premium
Noise-cancelling headphones249.0mid-range
Mechanical keyboard119.0mid-range
Deep Learning (book)89.0mid-range

6 rows

Related

Learn it step by step in the free lesson Functions: Text, Numbers, Dates & CASE, with exercises that check your queries.