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.

Skip to the 4 exercises ↓

Syntax

CASE
  WHEN salary >= 85000 THEN 'High'
  WHEN salary >= 55000 THEN 'Mid'
  ELSE 'Low'
END
WHEN 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 WHEN lines as you need; a condition can combine several tests with AND and OR.
ELSE 'Low'
The value when no WHEN was 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.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Live querySalary bands
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namesalaryband
Maya72000Mid
Omar58000Mid
Lena58000Mid
Raj95000High
Zoe88000High
Ivan81000Mid
Nora46000Low
Theo43000Low
Kim88000High

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.

Live queryWrong order: nobody is High
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namesalaryband
Maya72000Mid
Omar58000Mid
Lena58000Mid
Raj95000Mid
Zoe88000Mid
Ivan81000Mid
Nora46000Low
Theo43000Low
Kim88000Mid

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.

Live queryNo ELSE: Nora and Theo get NULL
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namesalaryband
Maya72000Mid
Omar58000Mid
Lena58000Mid
Raj95000High
Zoe88000High
Ivan81000Mid
Nora46000NULL
Theo43000NULL
Kim88000High

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.

Live queryHeadcount per department, side by side
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 1 row
salesengineeringsupport
342

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.

Live queryWHEN NULL: nobody is a department head
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namemanager_idrole
MayaNULLHas a manager
Omar1Has a manager
Lena1Has a manager
RajNULLHas a manager
Zoe4Has a manager
Ivan4Has a manager
NoraNULLHas a manager
Theo7Has a manager
Kim4Has a manager

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.

Live querySupport first, then Sales, then the rest
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namedepartment
NoraSupport
TheoSupport
LenaSales
MayaSales
OmarSales
IvanEngineering
KimEngineering
RajEngineering
ZoeEngineering

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.

Exercise 1Head or Team

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name,
  CASE WHEN manager_id IS NULL THEN 'Head' ELSE 'Team' END
FROM employees
Result · 9 rows
nameCASE WHEN manager_id IS NULL THEN 'Head' ELSE 'Team' END
MayaHead
OmarTeam
LenaTeam
RajHead
ZoeTeam
IvanTeam
NoraHead
TheoTeam
KimTeam

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.

Exercise 2Three levels, two conditions in one 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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name,
  CASE
    WHEN department = 'Engineering' AND salary >= 85000 THEN 'A'
    WHEN salary >= 70000 THEN 'B'
    ELSE 'C'
  END
FROM employees
Result · 9 rows
nameCASE WHEN department = 'Engineering' AND salary >= 85000 THEN 'A' WHEN salary >= 70000 THEN 'B' ELSE 'C' END
MayaB
OmarC
LenaC
RajA
ZoeA
IvanB
NoraC
TheoC
KimA

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.

Exercise 3Two counts per department

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
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
Result · 3 rows
departmentSUM(CASE WHEN salary >= 60000 THEN 1 ELSE 0 END)SUM(CASE WHEN salary < 60000 THEN 1 ELSE 0 END)
Engineering40
Sales12
Support02

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.

Exercise 4Department heads first

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, department
FROM employees
ORDER BY CASE WHEN manager_id IS NULL THEN 0 ELSE 1 END, name
Result · 9 rows
namedepartment
MayaSales
NoraSupport
RajEngineering
IvanEngineering
KimEngineering
LenaSales
OmarSales
TheoSupport
ZoeEngineering

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.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

SQL NULL: IS NULL, COALESCE and the NOT IN trapSQL GROUP BY, with live queriesSQL COUNT(DISTINCT), with live queriesSQL interview questions, with answersSQL GROUP BY practiceSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: Conditional Expressions (CASE), SQLite documentation: SQL Language Expressions (the CASE expression). Last checked November 2026.