Conditional Logic in Queries

Article · 2:00

CASE and IF, and the pivot they make possible.

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;