CASE WHEN: if-then logic inside a query

Step 1 Understand it

Why you need it. 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 (Phase 2) and to pivoting.

Watch it happen

Picture it

flowchart TD
  R["one row: salary"] --> A{"salary 80000 or more?"}
  A -->|"yes"| H["high"]
  A -->|"no"| B{"salary 50000 or more?"}
  B -->|"yes"| M["medium"]
  B -->|"no"| L["ELSE: low"]

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.

The shape of it

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

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

SELECT name, salary,
       CASE
         WHEN salary >= 80000 THEN 'high'
         WHEN salary >= 50000 THEN 'medium'
         ELSE 'low'
       END AS band
FROM employees
ORDER BY salary DESC, name;

SELECT order_id, status,
       CASE status
         WHEN 'delivered' THEN 'closed'
         WHEN 'cancelled' THEN 'closed'
         ELSE 'open'
       END AS state
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

SELECT name, CASE WHEN dept_id = 1 THEN 'engineering' END AS team
FROM employees
WHERE salary <= 70000
ORDER BY name;

Must-know points

• CASE returns the THEN value of the first true WHEN, then stops.
• No ELSE means NULL for rows that match nothing.
• Always finish with END, and name the result with AS.
• CASE is an expression: it works in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates.
• Every THEN should return the same type — all text, or all numbers.

Common mistakes

• Putting the loose condition first (>= 50000 before >= 80000), so the strict one is never reached.
• Forgetting END, which gives a syntax error.
• Writing CASE status WHEN NULL THEN …. The short form uses =, so it never matches NULL. Use CASE WHEN status IS NULL THEN ….

In other databases

• MySQL and SQLite also have IF(condition, a, b) / IIF(condition, a, b), and SQL Server has IIF. CASE works everywhere, so learn CASE.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL CASE Expression

  short page with a try-it editor, free

• Read

  Mode SQL Tutorial — SQL CASE

  Mode, free

• Read

  PostgreSQL docs — Conditional Expressions

  official documentation, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why the order of the WHEN lines matters, using the salary bands.

   Show answer

   CASE checks the WHEN lines from top to bottom and stops at the first one that is true. So the most specific test must come first.

   The right order is WHEN salary >= 80000 THEN 'high', then WHEN salary >= 50000 THEN 'medium', then ELSE 'low'.

   Swap the first two lines and Asha (95,000) meets >= 50000 first. That is true, so she becomes 'medium', and the 'high' line is never reached for anyone. The query below has the lines swapped: nobody is high.

   sqlCopy

   SELECT name, salary,
          CASE
            WHEN salary >= 50000 THEN 'medium'
            WHEN salary >= 80000 THEN 'high'
            ELSE 'low'
          END AS band
   FROM employees
   ORDER BY salary DESC, name;

   Try it

   8 rows

   name	salary	band
   Asha	95000	medium
   Hana	80000	medium
   Ben	70000	medium
   Chen	70000	medium
   Dara	60000	medium
   Fatima	52000	medium
   Eli	45000	low
   Gus	38000	low

2. Writefrom a blank page, then run it

   Write a query that labels each product as 'cheap' (under £10), 'mid' (£10 to £29) or 'premium' (£30 and up), sorted by price.

   Show answer

   Test the cheapest band first; then each line only needs an upper limit. "Under £10" is < 10, and "£10 to £29" is < 30 once the first line has taken the cheap ones.

   sqlCopy

   SELECT product_name, price,
          CASE
            WHEN price < 10 THEN 'cheap'
            WHEN price < 30 THEN 'mid'
            ELSE 'premium'
          END AS price_band
   FROM products
   ORDER BY price;

   Try it

   7 rows

   product_name	price	price_band
   Pen	2	cheap
   Notebook	5	cheap
   Mug	8	cheap
   Phone case	15	mid
   Desk lamp	25	mid
   Backpack	30	premium
   Headphones	40	premium

3. Predictsay it before you run it

   What state does each of Tom's orders get in the short-form example? (Change customer_id to 2 in your head.)

   Show answer

   Tom (customer 2) has two orders, 104 and 108. Both are delivered, so both are 'closed'.

   sqlCopy

   SELECT order_id, status,
          CASE status
            WHEN 'delivered' THEN 'closed'
            WHEN 'cancelled' THEN 'closed'
            ELSE 'open'
          END AS state
   FROM orders
   WHERE customer_id = 2
   ORDER BY order_id;

   Try it

   2 rows

   order_id	status	state
   104	delivered	closed
   108	delivered	closed

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Order 106 has a NULL amount. Write a CASE that labels orders 'big' (over £100), 'small', or 'unknown' when the amount is missing.

   Show answer

   Test for NULL first. If you don't, NULL > 100 is unknown, not true, so order 106 falls through to ELSE and is wrongly labelled 'small'.

   sqlCopy

   SELECT order_id, amount,
          CASE
            WHEN amount IS NULL THEN 'unknown'
            WHEN amount > 100 THEN 'big'
            ELSE 'small'
          END AS size
   FROM orders
   ORDER BY order_id;

   Try it

   10 rows

   order_id	amount	size
   101	120	big
   102	80	small
   103	45	small
   104	200	big
   105	150	big
   106	NULL	unknown
   107	60	small
   108	75	small
   109	30	small
   110	33	small

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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, to sort in a custom order, and most of all inside aggregates, like SUM of CASE WHEN status equals delivered THEN 1, to count several categories in a single pass.

Follow-up questions

Tick each check when you can do it without notes