CASE, COALESCE and NULLIF

Bucketing, labelling, pivoting and safe division all need if-then logic inside SQL.

CASE is SQL's if/else expression. It returns a value, so it can appear anywhere an expression can: in SELECT, WHERE, ORDER BY, even inside aggregates.

The searched form tests conditions in order and returns the first match:

CASE
    WHEN total >= 100 THEN 'large'
    WHEN total >= 20  THEN 'medium'
    ELSE 'small'
END

The simple form compares one expression with values: CASE status WHEN 'shipped' THEN 1 WHEN 'pending' THEN 2 ELSE 3 END. Without ELSE, unmatched rows get NULL. All branches must produce compatible types.

Inside an aggregate, CASE lets one pass compute several conditional totals, which is the portable way to pivot rows into columns:

SUM(CASE WHEN status = 'shipped' THEN total ELSE 0 END) AS shipped_value

Two shorthands handle NULL:

COALESCE(a, b, c) returns the first non-NULL argument. Use it for defaults: COALESCE(city, 'unknown'). It is standard; IFNULL (MySQL, SQLite), ISNULL (SQL Server) and NVL (Oracle) are two-argument dialect versions.
NULLIF(a, b) returns NULL if a = b, otherwise a. Its classic use is avoiding division by zero: x / NULLIF(y, 0) yields NULL instead of an error.

PostgreSQL, MySQL and Oracle also have GREATEST and LEAST for the largest or smallest of several values. Note that in PostgreSQL they ignore NULL arguments, while in MySQL and Oracle any NULL makes the result NULL.

CASE is evaluated in order and stops at the first true WHEN, so put the narrower conditions first.

Example

SELECT id, total,
       CASE WHEN total >= 100 THEN 'large'
            WHEN total >= 20  THEN 'medium'
            ELSE 'small'
       END AS size
FROM orders ORDER BY id;
--  101 |  68.50 | medium
--  102 | 189.00 | large
--  103 |   9.99 | small
--  104 |  49.00 | medium
--  105 | 238.00 | large

-- Pivot: one row per customer, a column per status
SELECT customer_id,
       SUM(CASE WHEN status = 'shipped' THEN total ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'pending' THEN total ELSE 0 END) AS pending
FROM orders GROUP BY customer_id ORDER BY customer_id;
--  1 |  68.50 | 189.00
--  2 | 247.99 |   0
--  3 |   0    |   0

SELECT 10 / NULLIF(0, 0) AS safe_div;   -- NULL, not a division error

Remember

CASE returns the value of the first true WHEN, else ELSE (or NULL)
CASE inside SUM/COUNT pivots rows into columns
COALESCE supplies defaults; NULLIF(y, 0) prevents division by zero

Watch out for

Omitting ELSE and being surprised by NULLs in the result.

Check yourself

What does CASE WHEN total > 10 THEN 'a' WHEN total > 100 THEN 'b' END give for total = 238?
What does COALESCE(NULL, NULL, 'x', 'y') return?

Official docs: www.postgresql.org/docs/current/functions-conditional.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks