CASE & Conditional Logic · SQL & Databases

CASE is SQL's if/else. It labels rows into categories and, combined with aggregates, pivots data into summary columns.

The CASE expression is SQL's conditional logic — the equivalent of if/else. It evaluates conditions in order and returns the first matching result. It works anywhere an expression is allowed: in SELECT to label rows, in ORDER BY for custom sorts, and inside aggregates for powerful conditional summaries.

CASE WHEN ... THEN ... ELSE ... END

CASE checks each WHEN condition top to bottom and returns the THEN value of the first that's true; if none match, it returns the ELSE value (or NULL if there's no ELSE).

Label salaries into bands

SELECT name, salary,
       CASE WHEN salary >= 80000 THEN 'High'
            WHEN salary >= 55000 THEN 'Mid'
            ELSE 'Low'
       END AS band
FROM   employees;

Conditional aggregation (pivot-style counts)

SELECT dept_id,
       SUM(CASE WHEN salary >= 80000 THEN 1 ELSE 0 END) AS high_earners,
       SUM(CASE WHEN salary <  55000 THEN 1 ELSE 0 END) AS low_earners
FROM   employees
GROUP  BY dept_id;

⚡ The edge

CASE returns the first matching WHEN, so order your conditions from most specific to most general. No ELSE means unmatched rows get NULL.
SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the standard trick to count rows meeting a condition per group — effectively pivoting rows into summary columns.

Worked example

Count, per department, how many employees earn at least 60000.

For each row, produce 1 if salary >= 60000, else 0, using CASE.
Sum those 1s and 0s within each department to get the count.
Group by dept_id so you get one row per department.

Answer: SELECT dept_id, SUM(CASE WHEN salary >= 60000 THEN 1 ELSE 0 END) AS cnt FROM employees GROUP BY dept_id;

Worked example

Why does a CASE with no ELSE sometimes return NULL?

CASE returns the THEN value of the first matching WHEN.
If no WHEN matches and there is no ELSE, there is nothing to return.
In that situation CASE yields NULL — add an ELSE to provide a default.

Answer: With no matching WHEN and no ELSE, CASE returns NULL; add ELSE for a default.

⚠ Watch out

WHEN order matters — the first true condition wins, so put specific conditions before general ones.
A missing ELSE yields NULL for unmatched rows.
Don't confuse COALESCE (first non-null) and NULLIF (null if equal) with CASE — they're shortcuts for specific CASE patterns.