CASE is SQL's conditional expression, and it works anywhere a value is allowed - in the SELECT list, in ORDER BY, in WHERE, inside an aggregate. It comes in two forms: the simple form compares one expression against values, and the searched form evaluates a condition per branch, which is the more useful of the two. Without an ELSE, an unmatched row yields NULL.
IF(condition, then, else) is the two-branch shorthand. IFNULL(a, b) substitutes when a value is NULL, COALESCE does the same for a list and returns the first non-NULL, and NULLIF(a, b) returns NULL when two values are equal - which is the standard trick for avoiding a division by zero.
The pattern worth learning properly is conditional aggregation: putting a CASE inside SUM or COUNT to count only the rows that match. SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) counts paid orders within a group, and several of those in one SELECT turn rows into columns - a pivot, in one pass over the table, instead of one query per column.
A caution: CASE in a WHERE clause usually means a condition that could be written as plain AND and OR, and the plain version is both clearer and more likely to use an index.
-- Searched CASE: a condition per branch, evaluated in order.
SELECT reference,
CASE
WHEN total >= 1000 THEN 'large'
WHEN total >= 100 AND status = 'paid' THEN 'medium, settled'
WHEN total >= 100 THEN 'medium'
ELSE 'small' -- without ELSE, NULL
END AS band
FROM orders;
-- CASE works in ORDER BY too: a custom sort order.
SELECT reference, status FROM orders
ORDER BY CASE status
WHEN 'pending' THEN 1
WHEN 'paid' THEN 2
WHEN 'shipped' THEN 3
ELSE 4
END,
placed_at DESC;
-- NULLIF avoids division by zero: x / NULL is NULL, not an error.
SELECT SUM(total) / NULLIF(COUNT(*), 0) AS average_order FROM orders;
-- Conditional aggregation: rows into columns, in one pass.
SELECT DATE_FORMAT(placed_at, '%Y-%m') AS month,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS revenue
FROM orders
GROUP BY month
ORDER BY month DESC;