conditional
CASE WHEN
SQL's portable if/else-if expression. Evaluates each WHEN clause in order and returns the first matching result; returns NULL from an unmatched CASE without ELSE.
CASE WHEN cond1 THEN result1 [WHEN ...] [ELSE default] ENDParameters / Modifiers
| Parameter | Purpose |
|---|---|
| Searched CASE | CASE WHEN cond THEN ... — each condition is a boolean expression |
| Simple CASE | CASE expr WHEN value THEN ... — compares expr to each value |
| ELSE | Default result when no WHEN matches (defaults to NULL if omitted) |
| FILTER (WHERE ...) | Postgres shorthand replacing CASE inside aggregates |
Examples
SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 75 THEN 'B' ELSE 'C' END FROM tests;Letter grade per row
SELECT SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) FROM invoices;Conditional sum without a WHERE clause
SELECT CASE country WHEN 'US' THEN 'North America' WHEN 'IN' THEN 'Asia' ELSE 'Other' END FROM users;Simple CASE form
SELECT COUNT(*) FILTER (WHERE status = 'paid') FROM invoices;Postgres shorthand for the same conditional aggregation as example 2
Dialect notes / Gotcha
Every branch must produce a compatible type — mixing text and int raises an error. Order of WHEN clauses matters; the first true one wins.
Related functions
COALESCE
Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.
NULLIF
Returns NULL if value1 equals value2, otherwise returns value1. Handy for converting sentinel values (empty strings, 0) into NULL, or safely guarding against division by zero.
GREATEST / LEAST
Return the largest (GREATEST) or smallest (LEAST) of the provided arguments row-by-row — not across rows, which is MIN/MAX. NULL handling differs by engine: Postgres ignores NULL args, MySQL returns NULL if any argument is NULL.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet