CASE Expressions: Conditional Logic in SQL

Overview

A CASE expression is SQL's inline if/else: it evaluates a series of conditions in order and returns the value tied to the first one that's true, falling back to ELSE if none match. It behaves like a normal column in SELECT, so you can name it with AS, sort by it, filter on it, or nest it inside an aggregate function.

Common real-world uses: turning a numeric value into a labeled category (salary band, age group, price tier), or building a conditional count/sum without writing several separate queries.

The Query

Step-by-Step Breakdown

1. CASE — starts the expression.
2. WHEN salary > 100000 THEN 'High' — checked first. If true, the expression evaluates to 'High' and no further branches are checked for that row.
3. WHEN salary > 75000 THEN 'Mid' — only reached if the first condition was false. Order matters: this branch implicitly means "salary is 75,000 or less but this line wasn't reached unless the first WHEN already failed," so it correctly covers the 75,000–100,000 range.
4. ELSE 'Entry' — the fallback for every row that didn't match any WHEN above.
5. END AS salary_band — closes the expression and names the resulting column.

Because WHEN clauses are evaluated top to bottom and the first match wins, the order of your conditions is part of the logic, not just style.

Variations

Use CASE inside an aggregate to conditionally count rows:

CASE WHEN salary > 100000 THEN 1 END returns 1 for qualifying rows and NULL (via the implicit, unwritten ELSE) for every other row. COUNT() ignores NULLs, so COUNT(CASE WHEN ... THEN 1 END) effectively counts only the rows matching the condition — a common way to compute several conditional totals in one pass over the data instead of running separate filtered queries.

Common Mistakes

• Ordering WHEN clauses incorrectly. Putting WHEN salary > 75000 before WHEN salary > 100000 would mean every high earner also satisfies the first, broader condition — they'd all end up labeled 'Mid' and the 'High' branch would never be reached.
• Forgetting CASE returns a single value per row, not a filter. A CASE expression labels or transforms a row; it doesn't remove rows from the result the way WHERE does.
• Mixing incompatible return types across branches. Every THEN (and ELSE) value should resolve to a comparable type — mixing text and numbers across branches can force unexpected type coercion.
• Using COUNT(CASE WHEN x THEN 1 ELSE 0 END) instead of omitting ELSE. Both produce the same count in most databases, but COUNT(CASE WHEN x THEN 1 END) (no ELSE) is the clearer idiom, since it relies on COUNT ignoring NULL rather than summing zeros.