After this lesson you can put decisions inside a query. You can turn salaries into bands, labels such as Ongoing or Finished into a report, and conditional counts into one tidy row. CASE is the tool SQL gives you for any "if this, then that" requirement, and it works the same on every database.
All the examples run on the sample company database, tested on SQLite.
The searched CASE
A CASE expression looks at conditions one at a time and returns a result for the first one that is true:
SELECT first_name,
salary,
CASE
WHEN salary >= 120000 THEN 'High'
WHEN salary >= 70000 THEN 'Medium'
ELSE 'Entry'
END AS band
FROM employees
ORDER BY id
LIMIT 8;+------------+--------+--------+
| first_name | salary | band |
+------------+--------+--------+
| Meera | 180000 | High |
| Arjun | 125000 | High |
| Kavya | 125000 | High |
| Rohan | 82000 | Medium |
| Isha | 78000 | Medium |
| Vikram | 150000 | High |
| Neha | 90000 | Medium |
| Sameer | 90000 | Medium |
+------------+--------+--------+Read it like a ladder. A salary of 125000 meets the first test and stops there. A salary of 82000 fails the first, passes the second, and stops. Only people who fail both tests reach the ELSE. The word END closes the expression, and AS band names the new column.
Because the branches are tried in order, the order matters. If you swapped the two WHEN lines, every salary of 70000 or more would get Medium, since the broader test would catch everyone before the High test is reached. Put the narrowest condition first.
The simple CASE
When you are comparing one column against a list of exact values, a shorter form is available:
SELECT first_name,
department_id,
CASE department_id
WHEN 1 THEN 'Engineering'
WHEN 2 THEN 'Sales'
WHEN 3 THEN 'Marketing'
END AS dept_label
FROM employees
WHERE id IN (1, 6, 10, 13, 18);+------------+---------------+-------------+
| first_name | department_id | dept_label |
+------------+---------------+-------------+
| Meera | 1 | Engineering |
| Vikram | 2 | Sales |
| Dev | 3 | Marketing |
| Lata | 4 | NULL |
| Ravi | NULL | NULL |
+------------+---------------+-------------+Two things stand out in the output. The finance employee, with department 4, gets NULL, because no branch matches and there is no ELSE. And Ravi, who has no department, also gets NULL.
The second point needs care. The simple form compares with =, and comparing NULL with anything, even another NULL, is never true. So WHEN NULL THEN ... can never match. To handle missing values, use the searched form with IS NULL:
SELECT first_name,
CASE
WHEN department_id IS NULL THEN 'Unassigned'
WHEN department_id = 1 THEN 'Engineering'
ELSE 'Other department'
END AS dept_label
FROM employees
WHERE id IN (1, 6, 18);+------------+------------------+
| first_name | dept_label |
+------------+------------------+
| Meera | Engineering |
| Vikram | Other department |
| Ravi | Unassigned |
+------------+------------------+Always decide your ELSE
If you leave out ELSE, unmatched rows become NULL. Sometimes that is what you want, but more often it produces blank cells that someone has to explain. Write the ELSE yourself, even if it only says 'Unknown'. All the results in a CASE should also be of a similar type, either all text or all numbers, because the whole expression produces one column.
CASE inside a report
Because CASE is an expression, you can use it wherever an expression fits. Here it labels projects:
SELECT name,
CASE WHEN end_date IS NULL THEN 'Ongoing' ELSE 'Finished' END AS status
FROM projects;+-------------------+----------+
| name | status |
+-------------------+----------+
| Customer Portal | Finished |
| Mobile App | Ongoing |
| Spring Campaign | Finished |
| Q3 Sales Push | Finished |
| Billing Migration | Ongoing |
+-------------------+----------+You can also group by it. This counts how many employees fall in each salary band. Repeating the full expression in GROUP BY works on every database:
SELECT CASE
WHEN salary >= 120000 THEN 'High'
WHEN salary >= 70000 THEN 'Medium'
ELSE 'Entry'
END AS band,
COUNT(*) AS people
FROM employees
GROUP BY CASE
WHEN salary >= 120000 THEN 'High'
WHEN salary >= 70000 THEN 'Medium'
ELSE 'Entry'
END
ORDER BY MIN(salary);+--------+--------+
| band | people |
+--------+--------+
| Entry | 7 |
| Medium | 9 |
| High | 4 |
+--------+--------+SQLite, MySQL and PostgreSQL also let you write GROUP BY band using the alias, which is shorter. SQL Server does not accept the alias in GROUP BY, so the long form is the portable one. The ORDER BY MIN(salary) sorts the bands from lowest to highest pay. You will learn about aggregates like MIN in the section on summarising data.
Conditional counting
One of the most useful tricks in reporting is combining CASE with SUM to count rows that meet a condition, several conditions at a time, in a single pass:
SELECT SUM(CASE WHEN salary >= 100000 THEN 1 ELSE 0 END) AS earning_100k_plus,
SUM(CASE WHEN salary < 100000 THEN 1 ELSE 0 END) AS earning_under_100k,
COUNT(*) AS everyone
FROM employees;+-------------------+--------------------+----------+
| earning_100k_plus | earning_under_100k | everyone |
+-------------------+--------------------+----------+
| 6 | 14 | 20 |
+-------------------+--------------------+----------+Each row contributes a 1 to one of the first two columns. The CASE turns a condition into a number, and SUM adds the numbers. The same idea works for sums of money: SUM(CASE WHEN ... THEN amount ELSE 0 END).
Custom sort orders
CASE in ORDER BY lets you sort in an order that has nothing to do with alphabet or size. Suppose a report should list people with no department first, and then everyone else by salary, highest first:
SELECT first_name, department_id, salary
FROM employees
ORDER BY CASE WHEN department_id IS NULL THEN 0 ELSE 1 END,
salary DESC
LIMIT 4;+------------+---------------+--------+
| first_name | department_id | salary |
+------------+---------------+--------+
| Ravi | NULL | 95000 |
| Meera | 1 | 180000 |
| Vikram | 2 | 150000 |
| Arjun | 1 | 125000 |
+------------+---------------+--------+The first sort key is 0 for the unassigned person and 1 for everybody else, so the unassigned row comes first. The second key breaks ties by salary.
Differences between databases
CASE itself is standard and works the same in MySQL, PostgreSQL, SQLite and SQL Server. The differences are around it:
- Shortcuts. MySQL has
IF(condition, a, b), SQL Server hasIIF(condition, a, b), and SQLite acceptsiifin recent versions. PostgreSQL has neither.CASEworks everywhere, so prefer it when you want portable code. - Alias in GROUP BY. As shown above, SQL Server does not accept it.
- Alias in ORDER BY. This works in all four databases, as in
ORDER BY band.
For the MySQL-specific shortcuts, see CASE, IF, IFNULL and COALESCE in MySQL.
Common mistakes
- Wrong order of branches. A broad test first hides the narrower ones. Test the most specific condition first.
- Forgetting
END. The most common syntax error withCASE. - Using
WHEN NULL. It never matches. UseWHEN column IS NULL. - Leaving out
ELSE. You getNULLfor every unmatched row without any warning. - Mixing result types. Returning
'High'in one branch and1in another makes the column's type unpredictable, and some databases refuse it.
Exercise
For every project, show its name, its budget and a column size that says 'Large' for a budget of 500000 or more, 'Medium' for 200000 up to (but not including) 500000, and 'Small' otherwise. Then write a second query that shows, in one row, how many projects are large, medium and small, using SUM and CASE.
Show one possible answer
SELECT name,
budget,
CASE
WHEN budget >= 500000 THEN 'Large'
WHEN budget >= 200000 THEN 'Medium'
ELSE 'Small'
END AS size
FROM projects
ORDER BY budget DESC;+-------------------+--------+--------+
| name | budget | size |
+-------------------+--------+--------+
| Mobile App | 650000 | Large |
| Customer Portal | 400000 | Medium |
| Billing Migration | 300000 | Medium |
| Q3 Sales Push | 200000 | Medium |
| Spring Campaign | 120000 | Small |
+-------------------+--------+--------+SELECT SUM(CASE WHEN budget >= 500000 THEN 1 ELSE 0 END) AS large,
SUM(CASE WHEN budget >= 200000 AND budget < 500000 THEN 1 ELSE 0 END) AS medium,
SUM(CASE WHEN budget < 200000 THEN 1 ELSE 0 END) AS small
FROM projects;+-------+--------+-------+
| large | medium | small |
+-------+--------+-------+
| 1 | 3 | 1 |
+-------+--------+-------+Key points
- CASE WHEN condition THEN result ... ELSE default END is SQL's if-then-else, and it is an expression, so it can appear in SELECT, WHERE, ORDER BY and inside aggregates.
- The database checks the WHEN branches from top to bottom and uses the first one that is true. Put the most specific test first.
- If no branch matches and there is no ELSE, the result is NULL. Always decide what your ELSE should be.
- SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts rows that meet a condition, and it is the standard way to build cross-tab style reports.