SQL practice › SQL reference › CASE WHEN
SQL CASE WHEN: multiple conditions, ELSE and NULL, with queries you can run
CASE is SQL's if/else. It checks its WHEN conditions from top to bottom and returns the THEN value of the first one that is true: CASE WHEN salary >= 85000 THEN 'High' WHEN salary >= 55000 THEN 'Mid' ELSE 'Low' END. If none is true you get the ELSE value, or NULL when there is no ELSE.
It is an expression: it gives one value per row, so it can go anywhere a value can, in SELECT, inside SUM or COUNT, in ORDER BY, in WHERE. The mistakes on this page all run without an error and return wrong labels or wrong counts.
Syntax
CASE
WHEN salary >= 85000 THEN 'High'
WHEN salary >= 55000 THEN 'Mid'
ELSE 'Low'
ENDWHEN salary >= 85000 THEN 'High'- A condition and the value to return when it is true. Checked first.
WHEN salary >= 55000 THEN 'Mid'- Checked only if the one above wasn't true. Add as many
WHENlines as you need; a condition can combine several tests withANDandOR. ELSE 'Low'- The value when no
WHENwas true. Optional: without it, those rows get NULL. END- Closes the expression. Give the column a name after it:
END AS band.
The table
Nine employees in three departments. Maya, Raj and Nora have no manager: their manager_id is NULL, which matters further down.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary | band |
|---|---|---|
| Maya | 72000 | Mid |
| Omar | 58000 | Mid |
| Lena | 58000 | Mid |
| Raj | 95000 | High |
| Zoe | 88000 | High |
| Ivan | 81000 | Mid |
| Nora | 46000 | Low |
| Theo | 43000 | Low |
| Kim | 88000 | High |
salary < 85000 written in the second line: a row only gets there if the first test failed.The first true WHEN wins
The branches are read in the order you wrote them, and CASE stops at the first one that is true. So the order is part of the logic. Put the wider condition first and the narrower one below it is never reached.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary | band |
|---|---|---|
| Maya | 72000 | Mid |
| Omar | 58000 | Mid |
| Lena | 58000 | Mid |
| Raj | 95000 | Mid |
| Zoe | 88000 | Mid |
| Ivan | 81000 | Mid |
| Nora | 46000 | Low |
| Theo | 43000 | Low |
| Kim | 88000 | Mid |
WHEN lines.A missing ELSE gives NULL
ELSE is optional. A row that matches no WHEN then gets NULL, not an empty text and not an error.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary | band |
|---|---|---|
| Maya | 72000 | Mid |
| Omar | 58000 | Mid |
| Lena | 58000 | Mid |
| Raj | 95000 | High |
| Zoe | 88000 | High |
| Ivan | 81000 | Mid |
| Nora | 46000 | NULL |
| Theo | 43000 | NULL |
| Kim | 88000 | High |
CASE what to return for them. Add ELSE 'Low' before END.Count by condition: CASE inside SUM or COUNT
Put a CASE inside an aggregate and each column counts only the rows that pass its own test. That turns groups into columns: a small pivot table, in one row.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| sales | engineering | support |
|---|---|---|
| 3 | 4 | 2 |
CASE gives 1 for the rows to count and 0 for the others, and SUM adds them up. With COUNT the ELSE 0 has to go: COUNT counts every value that isn't NULL, and 0 is a value. Change the first SUM to COUNT and sales becomes 9. COUNT(CASE WHEN department = 'Sales' THEN 1 END), with no ELSE, gives 3 again.CASE with NULL
CASE has a short form for comparing one expression with a list of values: CASE department WHEN 'Sales' THEN … WHEN 'Support' THEN … END. It compares with =, and nothing is equal to NULL, not even NULL. So WHEN NULL never matches.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | manager_id | role |
|---|---|---|
| Maya | NULL | Has a manager |
| Omar | 1 | Has a manager |
| Lena | 1 | Has a manager |
| Raj | NULL | Has a manager |
| Zoe | 4 | Has a manager |
| Ivan | 4 | Has a manager |
| Nora | NULL | Has a manager |
| Theo | 7 | Has a manager |
| Kim | 4 | Has a manager |
manager_id is NULL. To test for NULL, use the long form with IS NULL: CASE WHEN manager_id IS NULL THEN 'Department head' ELSE 'Has a manager' END.CASE in ORDER BY
Sorting by a column gives alphabetical or numeric order. When you need another order, such as one department first, sort by a CASE that turns each row into a number.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | department |
|---|---|
| Nora | Support |
| Theo | Support |
| Lena | Sales |
| Maya | Sales |
| Omar | Sales |
| Ivan | Engineering |
| Kim | Engineering |
| Raj | Engineering |
| Zoe | Engineering |
name, orders the people inside each department.Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this table and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns, the rows and the exact label texts do.
Return every employee's name and a second column with the text Head when the employee has no manager and Team when they have one.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name,
CASE WHEN manager_id IS NULL THEN 'Head' ELSE 'Team' END
FROM employees| name | CASE WHEN manager_id IS NULL THEN 'Head' ELSE 'Team' END |
|---|---|
| Maya | Head |
| Omar | Team |
| Lena | Team |
| Raj | Head |
| Zoe | Team |
| Ivan | Team |
| Nora | Head |
| Theo | Team |
| Kim | Team |
No manager means manager_id is NULL, and the only test that finds a NULL is IS NULL. That needs the long form of CASE, with the condition after WHEN.
Return every employee's name and a level: A for people in Engineering who earn 85000 or more, B for anyone else who earns 70000 or more, C for the rest.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name,
CASE
WHEN department = 'Engineering' AND salary >= 85000 THEN 'A'
WHEN salary >= 70000 THEN 'B'
ELSE 'C'
END
FROM employees| name | CASE WHEN department = 'Engineering' AND salary >= 85000 THEN 'A' WHEN salary >= 70000 THEN 'B' ELSE 'C' END |
|---|---|
| Maya | B |
| Omar | C |
| Lena | C |
| Raj | A |
| Zoe | A |
| Ivan | B |
| Nora | C |
| Theo | C |
| Kim | A |
One WHEN can hold several conditions joined with AND. The A test is the narrowest, so it goes first; an engineer on 88000 also passes salary >= 70000 and would be a B if that line came first.
For each department, return its name, how many people earn 60000 or more and how many earn less than 60000. Every department must appear, with 0 where there is nobody.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department,
SUM(CASE WHEN salary >= 60000 THEN 1 ELSE 0 END),
SUM(CASE WHEN salary < 60000 THEN 1 ELSE 0 END)
FROM employees
GROUP BY department| department | SUM(CASE WHEN salary >= 60000 THEN 1 ELSE 0 END) | SUM(CASE WHEN salary < 60000 THEN 1 ELSE 0 END) |
|---|---|---|
| Engineering | 4 | 0 |
| Sales | 1 | 2 |
| Support | 0 | 2 |
Both counts come from the same rows, so the split can't be done with WHERE. Each CASE turns a row into 1 or 0 for its own column, and SUM adds them per department. Engineering gets 4 and 0, Sales 1 and 2, Support 0 and 2.
Return every employee's name and department. The people with no manager come first, then everyone else; inside each of the two groups, sort by name from A to Z.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name, department
FROM employees
ORDER BY CASE WHEN manager_id IS NULL THEN 0 ELSE 1 END, name| name | department |
|---|---|
| Maya | Sales |
| Nora | Support |
| Raj | Engineering |
| Ivan | Engineering |
| Kim | Engineering |
| Lena | Sales |
| Omar | Sales |
| Theo | Support |
| Zoe | Engineering |
The CASE gives 0 to the three heads and 1 to everyone else, so sorting by it puts the heads on top; name then orders each group. Sorting by manager_id itself would order the second group by manager, not by name.
Questions people ask
How do I write CASE WHEN with multiple conditions?
Two ways, and they combine. Several outcomes: one WHEN … THEN … line per outcome, checked from top to bottom. Several tests for one outcome: join them inside one WHEN with AND or OR, as in WHEN department = 'Engineering' AND salary >= 85000 THEN 'A'.
What does CASE return when there is no ELSE?
NULL, for every row that matches no WHEN. That is useful inside COUNT, which skips NULLs, and a surprise in a label column. If every row should get a label, write the ELSE.
Can I use CASE WHEN in a WHERE clause?
Yes. CASE is an expression, so it can go wherever a value can. It earns its place in WHERE when the value to compare with depends on the row, as in WHERE salary >= CASE WHEN department = 'Sales' THEN 60000 ELSE 85000 END. For an ordinary filter, plain AND and OR are easier to read than a CASE.
How do I check for NULL in a CASE?
With the long form: CASE WHEN x IS NULL THEN … END. The short form, CASE x WHEN NULL THEN …, compares with = and never matches, as the live query above shows. The page on NULL has the rest.
Can the branches return different types?
Keep them the same type: all text, or all numbers. PostgreSQL's manual says the results of all the branches must be convertible to a single type. SQLite, which runs this page, accepts a number in one branch and text in another, so a query that works here can still be refused elsewhere.