SQL · Chapter 28 of 42

CASE Expression

CASE is SQL's if/else. It returns a value based on conditions.

Use for computed labels, conditional aggregates and row-level logic.

Syntax

CASE WHEN cond1 THEN val1 WHEN cond2 THEN val2 ELSE val3 END

Simple vs searched

Simple CASE compares one expression to values. Searched CASE has full boolean conditions per WHEN.

`SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END)` counts paid rows — great for pivots.

Example 1 (sql)

SELECT name,
  CASE WHEN age < 18 THEN 'Minor'
       WHEN age < 60 THEN 'Adult'
       ELSE 'Senior' END AS bucket
FROM users;

Output

name | bucket
Ana  | Adult
Ben  | Adult

Bucket by age.

Example 2 (sql)

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid
FROM orders;

Output

total | paid
100   | 87

Conditional count via SUM+CASE.

💡 Note: PostgreSQL and MySQL also have shorter alternatives: `NULLIF`, `COALESCE`, `IF()`.