CASE WHEN: if-then logic inside a query

CASE WHEN condition THEN value … ELSE value END returns a value chosen by the first condition that is true, so a query can label, group or tidy values row by row.

Why it matters

Data often needs sorting into buckets — salary bands, "open" or "closed" orders, "new" or "returning" customers — and those labels aren't stored anywhere. CASE builds them inside the query. It is also the key to counting several things in one pass (Unit 2) and to pivoting.

The idea

CASE is SQL's if-else. For each row, it checks the WHEN conditions from top to bottom and returns the THEN value of the first one that is true. If none is true, it returns the ELSE value — or NULL if there is no ELSE.

CASE is an expression, so it can go anywhere a value can: in SELECT, WHERE, ORDER BY, GROUP BY, or inside SUM and COUNT. The short form, CASE status WHEN 'shipped' THEN …, compares one column with several values.

Sorting post into pigeonholes. You read the address, check the first rule ("London?"), then the next ("Leeds?"), and drop the letter into the first hole that fits. Anything that fits no rule goes into "other" — that is ELSE.

A postal worker can put one letter in two holes by mistake. CASE never does: it stops at the first true WHEN, so the order of the WHENs matters. Put the strictest test first.

Syntax

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE other_value
END AS new_column

How it works

  1. For Asha (95000): salary >= 80000 is true, so the band is 'high', and CASE stops.
  2. For Ben (70000): the first test is false; salary >= 50000 is true, so 'medium'.
  3. For Eli (45000): both tests are false, so ELSE gives 'low'.
  4. If the tests were in the other order (>= 50000 first), Asha would wrongly get 'medium'. Order matters.
  5. END closes the CASE, and AS names the new column.

Key points

Common mistakes

Not true: The order of the WHEN branches in a CASE makes no difference to the result.
In fact: CASE stops at the first WHEN that is true. With >= 50000 written before >= 80000, the second branch is never reached: put the strictest test first.

In the interview

CASE is SQL's conditional expression: it evaluates WHEN conditions in order and returns the first matching THEN value, or the ELSE value, or NULL if there is no ELSE. Because it is an expression, it works anywhere. I use it to bucket values and to sort in a custom order. Most of all I use it inside aggregates, like SUM of CASE WHEN status equals delivered THEN 1, to count several categories in a single pass.

Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.