So far your queries have returned column values as-is. Conditional expressions let you compute a value per row — bucket numbers into labels, swap NULL for something printable, or dodge a divide-by-zero — all inside the SELECT itself, no application code needed.
The seed is a tiny help desk: one tickets table, deliberately riddled with NULLs — unassigned tickets, missing contact details, unrated satisfaction scores.
SELECT * FROM tickets ORDER BY id;
CASE: SQL's if/else
The searched form evaluates conditions top to bottom and returns the value of the first WHEN that's true. Perfect for bucketing a number into labels:
SELECT subject, satisfaction,
CASE
WHEN satisfaction >= 4 THEN 'happy'
WHEN satisfaction >= 3 THEN 'neutral'
WHEN satisfaction IS NOT NULL THEN 'unhappy'
END AS mood
FROM tickets
ORDER BY id;
Notice the unrated tickets: no WHEN matched (every comparison with NULL is unknown), there's no ELSE, so the result is NULL. A CASE without an ELSE yields NULL — add ELSE 'not rated yet' and run it again to see the difference.
The simple form: CASE expr WHEN value
When every branch compares the same expression against constants, the simple form is tidier — write the expression once, then list the values:
SELECT subject, status,
CASE status
WHEN 'open' THEN 'needs attention'
WHEN 'pending' THEN 'waiting on customer'
ELSE 'done'
END AS next_step
FROM tickets
ORDER BY id;
One trap: the simple form compares with =, and NULL = NULL is not true. So CASE col WHEN NULL THEN … never matches — when NULL is one of your branches, use the searched form with WHEN col IS NULL.
Custom sort orders
ORDER BY priority would sort alphabetically: high, low, , . Useless. A in the maps each value to the rank you actually want: