CASE

Returns a value chosen by the first matching condition: SQL's if / else if / else.

Syntax

CASE WHEN condition1 THEN result1
     [WHEN condition2 THEN result2 ...]
     [ELSE default]
END

CASE expr WHEN value1 THEN result1 [WHEN ...] [ELSE default] END   -- simple form

CASE is an expression, so it works anywhere a value does: the select list, WHERE, ORDER BY, GROUP BY, inside aggregates.

Conditions are tested in order; the first TRUE one wins. Put the most specific conditions first.
Without ELSE, unmatched rows get NULL.
All THEN/ELSE results must have a common type (THEN 1 ELSE 'none' is an error).
The simple form CASE status WHEN 'paid' THEN ... compares with =, so WHEN NULL never matches; use the searched form WHEN status IS NULL.
Inside an aggregate it makes conditional totals: SUM(CASE WHEN status = 'paid' THEN amount END) (PostgreSQL also has the cleaner FILTER clause).

Examples

Bucketing values starter

SELECT product_name, price,
       CASE WHEN price >= 200 THEN 'premium'
            WHEN price >= 100 THEN 'mid'
            ELSE 'budget'
       END AS price_band
FROM products
ORDER BY price DESC;

The 210 SAR product also satisfies price >= 100, but only the first matching branch counts.

Conditional aggregation starter

SELECT status,
       count(*) AS orders,
       sum(CASE WHEN quantity >= 2 THEN 1 ELSE 0 END) AS multi_unit_orders
FROM orders
GROUP BY status
ORDER BY status;

One pass over the table produces both counts per status.

Custom sort order starter

SELECT order_id, status
FROM orders
ORDER BY CASE status WHEN 'processing' THEN 1 WHEN 'shipped' THEN 2
                     WHEN 'delivered' THEN 3 ELSE 4 END,
         order_id;

Sorts by business workflow instead of alphabetically.

Pitfalls

Forgetting ELSE and getting NULLs (which then disappear from SUM or break concatenation).
Overlapping conditions in the wrong order (a broad condition first swallows the specific one).
Mixing result types in THEN branches.
Using CASE x WHEN NULL (never matches); write CASE WHEN x IS NULL.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: Also IIF(cond, a, b).
MySQL: Also IF(cond, a, b).
Snowflake: Also IFF(cond, a, b).
BigQuery: Also IF(cond, a, b).
Oracle: Also DECODE(expr, v1, r1, ..., default); DECODE treats two NULLs as equal.