SQL CASE
CASE is SQL's if/else expression. It evaluates conditions in order and returns the first matching value.
Searched CASE
SQL
SELECT name,
CASE
WHEN price < 20 THEN 'cheap'
WHEN price < 100 THEN 'mid'
ELSE 'pricey'
END AS tier
FROM products;
Simple CASE
SQL
SELECT name,
CASE country
WHEN 'AU' THEN 'Australia'
WHEN 'NZ' THEN 'New Zealand'
WHEN 'UK' THEN 'United Kingdom'
ELSE country
END AS country_name
FROM customers;
Conditional aggregation
One of the most useful patterns — pivot rows into columns:
SQL
SELECT SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid, SUM(CASE WHEN status = 'refunded' THEN total ELSE 0 END) AS refunded FROM orders;
Watch out for NULL
CASE x WHEN NULL THEN … never matches — x = NULL is never true. Use the searched form with IS NULL:
SQL
CASE WHEN phone IS NULL THEN 'no phone' ELSE phone END
Tip: If your
CASE only has one WHEN, IFNULL / COALESCE / NULLIF are usually shorter and clearer.Example
Example
SELECT name,
CASE
WHEN price < 20 THEN 'cheap'
WHEN price < 100 THEN 'mid'
ELSE 'pricey'
END AS tier
FROM products;
Try it Yourself »
Exercise
Close a CASE expression with…
CASE WHEN price < 20 THEN 'cheap' ELSE 'mid'
Three letters; the CASE terminator.
Discussion
Loading…