Loading…
Loading…
SQL tutorial · Intermediate
⏱️ 12 mins read
CASE WHEN adds conditional logic inside SQL — like if/else statements. It can be used in SELECT, WHERE, ORDER BY, and GROUP BY. Two forms: simple CASE (compares one value) and searched CASE (evaluates conditions). Always end with END.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END-- Categorize employees by salary
SELECT name, salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid-Level'
ELSE 'Junior'
END AS level
FROM employees;
-- Pivot-style: count by status in one row
SELECT
SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;
-- Custom sort order
SELECT id, status FROM orders
ORDER BY CASE status
WHEN 'pending' THEN 1
WHEN 'shipped' THEN 2
ELSE 3
END;| id | name | department_id | salary |
|---|---|---|---|
| 1 | Ada | 1 | 145000 |
| 2 | Grace | 1 | 115000 |
| 3 | Alan | 2 | 92000 |
| 4 | Edsger | NULL | 78000 |
| 5 | Barbara | 3 | 64000 |
| id | customer_id | status | total |
|---|---|---|---|
| 1 | 1 | shipped | 240.00 |
| 2 | 2 | shipped | 89.90 |
| 3 | 1 | pending | 430.00 |
| 4 | 3 | shipped | 59.50 |
| 5 | 4 | cancelled | 120.00 |
CASE is evaluated top-to-bottom and returns on the first TRUE branch. Swap the two salary bands above and every employee becomes 'Senior': Ada (145000) matches the first WHEN either way, but Grace (115000) — correctly Senior — would still hit 'Senior', while a 70000 earner would be labeled Senior instead of Mid-Level. Overlapping conditions make WHEN order load-bearing.
-- BROKEN: the ranges overlap, so everything >= 60000 reads 'Senior'
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid-Level' -- never reached for 60000-99999?
ELSE 'Junior'
END
-- Safe: make ranges mutually exclusive so order stops mattering
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 AND salary < 100000 THEN 'Mid-Level'
ELSE 'Junior'
ENDOmit ELSE and unmatched rows produce NULL — which SUM silently skips (so the pivot still works), but COUNT does not. COUNT counts non-NULL values, so adding ELSE 0 turns it from 'how many matched' into 'how many rows exist'. This one token is a top-tier interview trap.
SELECT
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count, -- 1
COUNT(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS with_else_zero, -- 5!!
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS sum_pending -- 1
FROM orders;
-- With ELSE 0 every row is non-NULL, so COUNT counts all five —
-- the zeros it was supposed to exclude are exactly what it countsThe searched form (CASE WHEN cond THEN ...) evaluates predicates; the simple form (CASE col WHEN v THEN ...) compares equality. NULL never matches either comparison — CASE department_id WHEN NULL would never fire — so the missing department drops into ELSE. That is exactly how we finally give Edsger a readable label.
SELECT name,
CASE department_id
WHEN 1 THEN 'Engineering'
WHEN 2 THEN 'Sales'
WHEN 3 THEN 'HR'
ELSE 'Unknown' -- catches dept 99 AND Edsger's NULL
END AS department_name
FROM employees;
-- Ada → Engineering ... Edsger → Unknown (NULL never matches WHEN 1)Forgetting the END keyword. Also: CASE evaluates conditions top-to-bottom and returns on the first TRUE match — order matters. If ELSE is omitted and no condition matches, CASE returns NULL.
The SUM(CASE WHEN...) pattern is essential for pivoting data — turning rows into columns. Interviewers at analytics companies ask this frequently. Know both the simple and searched CASE syntax.
1. How does CASE evaluate its WHEN branches?
2. If ELSE is omitted and no WHEN matches, CASE returns…?
3. COUNT(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) over ShopCo's orders returns…?
4. In CASE department_id WHEN 1 THEN 'Engineering' ... ELSE 'Unknown', where does Edsger's NULL land?
5. ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'shipped' THEN 2 ELSE 3 END achieves…?
6. SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) over ShopCo's orders returns…?
Return each employee's name and salary, plus a band column: 'high' for salaries of 90000 or more, 'mid' for 55000 or more, otherwise 'entry'.
⚡ Solve it in the SQL playground →Because ELSE 0 makes every row non-NULL, and COUNT counts non-NULL values — the zeros are counted too. Drop the ELSE so mismatches become NULL (skipped by COUNT), or use SUM with ELSE 0.
Everywhere — it is an expression, legal wherever expressions are. ORDER BY CASE is the idiomatic custom sort; GROUP BY CASE can bucket rows before aggregation; in WHERE it works but plain OR logic is often clearer.