CASE WHEN

Intermediate

⏱️ 12 mins read

Extends employees (Topic 1) and orders (Topic 7) — CASE is the tool that finally puts a label on Edsger's NULL department.

What You'll Learn

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.

Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Example

-- 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;

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1)
-- 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

-- orders (from Topic 7)
-- 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

First match wins — WHEN order is logic, not style

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'
END
Either order your WHENs from most to least specific, or make the conditions mutually exclusive — pick one discipline and apply it everywhere.

No ELSE means NULL — and ELSE 0 changes COUNT's answer

Omit 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 counts
For COUNT use CASE without ELSE (NULL rows are skipped); for SUM use ELSE 0. Mixing the two idioms silently corrupts the number.

Simple CASE vs searched CASE — and where NULL falls

The 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)
NULL fails every equality comparison, so it always falls to ELSE — which means 'Unknown' buckets in CASE output usually include your NULL rows. Name them deliberately (Topic 7).

Common Mistakes

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.

Interview Tips

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.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

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…?

Practice

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 →

Frequently Asked Questions

Why did my COUNT(CASE WHEN...THEN 1 ELSE 0 END) count every row?

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.

Can CASE appear in WHERE, GROUP BY, or ORDER BY?

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.

Back to Full Roadmap