The SQL CASE statement is a handy tool that allows us to add conditional logic to our queries. It’s particularly useful when we need to categorize or transform data based on multiple conditions.
In this article, we’ll explore how to use the CASE statement with multiple conditions, providing simple examples that should work across most major relational database management systems (RDBMSs).
Understanding the CASE Statement
The CASE statement in SQL is a conditional statement that allows us to specify conditions and return different values based on whether those conditions are met.
There are two main forms of the CASE statement:
- Simple
CASE: Compares an expression to a set of simple expressions to determine the result. - Searched
CASE: Evaluates a set of Boolean expressions to determine the result.
The syntax for a simple CASE statement goes like this:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
ENDThe basic syntax of a searched CASE statement goes something like this:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
ENDEach WHEN clause specifies a condition, and if that condition is true, the corresponding THEN result is returned. If none of the conditions are true, the ELSE result is returned. The ELSE clause is optional, but it’s generally a good idea to include it to handle unexpected cases.
Examples of each of the CASE types are below.
Sample Database
First, let’s set up a sample database that we can use to run our examples against:
-- Create the employees table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2),
years_experience INT,
department_id INT,
job_title VARCHAR(50)
);
-- Insert sample data
INSERT INTO employees (employee_id, name, salary, years_experience, department_id, job_title)
VALUES
(1, 'Shez Chaffer', 55000, 3, 1, 'Developer'),
(2, 'Bart Blackwood', 65000, 5, 1, 'Senior Developer'),
(3, 'Mike Johnson', 75000, 7, 1, 'Manager'),
(4, 'Emily Brown', 45000, 2, 2, 'Designer'),
(5, 'David Lee', 85000, 8, 2, 'Manager'),
(6, 'Sarah Wilson', 35000, 1, 3, 'Junior Analyst'),
(7, 'Tom Harris', 95000, 10, 3, 'CTO'),
(8, 'Lisa Anderson', 40000, 2, 1, 'QA Tester'),
(9, 'Robert Taylor', 50000, 4, 2, 'Marketing Specialist'),
(10, 'Jennifer Davis', 120000, 15, 1, 'CEO'),
(11, 'Kevin Martinez', 60000, 6, 3, 'Senior Analyst'),
(12, 'Amy Robinson', 48000, 3, 2, 'Content Writer'),
(13, 'Chris Evans', 70000, 7, 1, 'Product Manager'),
(14, 'Emma Thompson', 52000, 4, 3, 'Data Scientist'),
(15, 'Michael Scott', 62000, 5, 2, 'Sales Manager');
-- Create the departments table
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
-- Insert sample data for departments
INSERT INTO departments (department_id, department_name)
VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Finance');Now that we’ve got data, let’s run some examples.
Example 1: Categorizing Employees by Salary
Here’s a query that categorizes the employees based on their salary ranges:
SELECT
name,
salary,
CASE
WHEN salary < 50000 THEN 'Entry Level'
WHEN salary BETWEEN 50000 AND 75000 THEN 'Mid Level'
WHEN salary > 75000 THEN 'Senior Level'
ELSE 'Uncategorized'
END AS salary_category
FROM
employees;Result:
name salary salary_category
-------------- ------ ---------------
Shez Chaffer 55000 Mid Level
Bart Blackwood 65000 Mid Level
Mike Johnson 75000 Mid Level
Emily Brown 45000 Entry Level
David Lee 85000 Senior Level
Sarah Wilson 35000 Entry Level
Tom Harris 95000 Senior Level
Lisa Anderson 40000 Entry Level
Robert Taylor 50000 Mid Level
Jennifer Davis 120000 Senior Level
Kevin Martinez 60000 Mid Level
Amy Robinson 48000 Entry Level
Chris Evans 70000 Mid Level
Emma Thompson 52000 Mid Level
Michael Scott 62000 Mid Level
We can see that the value in the salary_category column is dependent on the outcome of the CASE statement.
Example 2: Combining Multiple Conditions
Suppose we want to categorize employees based on both their salary and years of experience. We can do this with multiple conditions within a single WHEN clause:
SELECT
name,
salary,
years_experience,
CASE
WHEN salary < 50000 AND years_experience < 3 THEN 'Junior'
WHEN (salary BETWEEN 50000 AND 75000) OR (years_experience BETWEEN 3 AND 6) THEN 'Intermediate'
WHEN salary > 75000 OR years_experience > 6 THEN 'Senior'
ELSE 'Uncategorized'
END AS employee_level
FROM
employees;Result:
name salary years_experience employee_level
-------------- ------ ---------------- --------------
Shez Chaffer 55000 3 Intermediate
Bart Blackwood 65000 5 Intermediate
Mike Johnson 75000 7 Intermediate
Emily Brown 45000 2 Junior
David Lee 85000 8 Senior
Sarah Wilson 35000 1 Junior
Tom Harris 95000 10 Senior
Lisa Anderson 40000 2 Junior
Robert Taylor 50000 4 Intermediate
Jennifer Davis 120000 15 Senior
Kevin Martinez 60000 6 Intermediate
Amy Robinson 48000 3 Intermediate
Chris Evans 70000 7 Intermediate
Emma Thompson 52000 4 Intermediate
Michael Scott 62000 5 Intermediate
Example 3: Using Subqueries in CASE Statements
CASE statements can also incorporate subqueries, allowing for even more complex conditions. Here’s an example where we categorize employees based on whether their salary is above or below the average for their department:
SELECT
e.name,
e.salary,
e.department_id,
d.department_name,
CASE
WHEN e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
) THEN 'Above Average'
WHEN e.salary < (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
) THEN 'Below Average'
ELSE 'Average'
END AS salary_comparison
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id;Result:
name salary department_id department_name salary_comparison
-------------- ------ ------------- --------------- -----------------
Shez Chaffer 55000 1 Engineering Below Average
Bart Blackwood 65000 1 Engineering Below Average
Mike Johnson 75000 1 Engineering Above Average
Emily Brown 45000 2 Marketing Below Average
David Lee 85000 2 Marketing Above Average
Sarah Wilson 35000 3 Finance Below Average
Tom Harris 95000 3 Finance Above Average
Lisa Anderson 40000 1 Engineering Below Average
Robert Taylor 50000 2 Marketing Below Average
Jennifer Davis 120000 1 Engineering Above Average
Kevin Martinez 60000 3 Finance Below Average
Amy Robinson 48000 2 Marketing Below Average
Chris Evans 70000 1 Engineering Below Average
Emma Thompson 52000 3 Finance Below Average
Michael Scott 62000 2 Marketing Above Average
Example 4: Using CASE in ORDER BY Clause
CASE statements are not limited to the SELECT list; they can also be used in other parts of a query, such as the ORDER BY clause. Here’s an example that sorts employees by a custom order based on their job title:
SELECT
name,
job_title
FROM
employees
ORDER BY
CASE
WHEN job_title = 'CEO' THEN 1
WHEN job_title = 'CTO' THEN 2
WHEN job_title = 'Manager' THEN 3
WHEN job_title LIKE '%Developer' THEN 4
ELSE 5
END,
name;Result:
name job_title
-------------- --------------------
Jennifer Davis CEO
Tom Harris CTO
David Lee Manager
Mike Johnson Manager
Bart Blackwood Senior Developer
Shez Chaffer Developer
Amy Robinson Content Writer
Chris Evans Product Manager
Emily Brown Designer
Emma Thompson Data Scientist
Kevin Martinez Senior Analyst
Lisa Anderson QA Tester
Michael Scott Sales Manager
Robert Taylor Marketing Specialist
Sarah Wilson Junior Analyst
This query sorts the results with the CEO first, followed by the CTO, Managers, Developers, and then all other job titles, with employees in each category sorted alphabetically by name.
Example 5: Using a Simple CASE Statement
All of the previous examples use searched CASE statements. As mentioned, there are also simple CASE statements, which compare an expression to a set of simple expressions. Let’s use a simple CASE statement for this example.
Suppose all employees are going on a field trip. But each department has a different activity for their field trip. So for our query, we want to display each employee along with their respective activity, based on the department that the employee belongs to.
In this case, we can use a simple CASE statement like this:
SELECT
name,
department_id,
CASE department_id
WHEN 1 THEN 'Paintball'
WHEN 2 THEN 'Rock Climbing'
WHEN 3 THEN 'Kayaking'
ELSE 'World Trip'
END AS activity
FROM
employees;Result:
name department_id activity
-------------- ------------- -------------
Shez Chaffer 1 Paintball
Bart Blackwood 1 Paintball
Mike Johnson 1 Paintball
Emily Brown 2 Rock Climbing
David Lee 2 Rock Climbing
Sarah Wilson 3 Kayaking
Tom Harris 3 Kayaking
Lisa Anderson 1 Paintball
Robert Taylor 2 Rock Climbing
Jennifer Davis 1 Paintball
Kevin Martinez 3 Kayaking
Amy Robinson 2 Rock Climbing
Chris Evans 1 Paintball
Emma Thompson 3 Kayaking
Michael Scott 2 Rock Climbing
In this example, the CASE statement compares the department_id to each WHEN clause. If there’s a match, it returns the corresponding THEN value. If no match is found, it returns the ELSE value.
The simple CASE is more concise when you’re just comparing a single expression against multiple possible values. However, it’s less flexible than the searched CASE, which can handle multiple conditions and more complex logic.
It’s worth noting that while this simple CASE example works, in a real-world scenario, we should check to see if the activities are already in the database. Or if they’re not, decide whether it would be a good idea to add them. That way we could return the same results with a different query – one in which we don’t need to hard code any data.
As an example of what I mean, suppose we simply wanted to return each employee’s department name instead of the activity. Given the department name is already in the database, we could construct a query that returns that, without needing to use a CASE statement. For example, we could use a join to retrieve the data from another table.
Here’s how that might look:
SELECT
e.name,
e.department_id,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id;Result:
name department_id department_name
-------------- ------------- ---------------
Shez Chaffer 1 Engineering
Bart Blackwood 1 Engineering
Mike Johnson 1 Engineering
Emily Brown 2 Marketing
David Lee 2 Marketing
Sarah Wilson 3 Finance
Tom Harris 3 Finance
Lisa Anderson 1 Engineering
Robert Taylor 2 Marketing
Jennifer Davis 1 Engineering
Kevin Martinez 3 Finance
Amy Robinson 2 Marketing
Chris Evans 1 Engineering
Emma Thompson 3 Finance
Michael Scott 2 Marketing
So the layout of the result set is similar to the excursion example from above. The difference is that we achieved this one without having to hardcode any data into a CASE statement.
The join approach is generally preferred as it’s more flexible and easier to maintain, especially when dealing with larger numbers of departments or frequent changes to department names.
See my SQL Joins Tutorial for a rundown on the various join types, with examples of each.
Best Practices and Considerations
- Always include an
ELSEclause in yourCASEstatements to handle unexpected cases. - Be mindful of the order of your
WHENclauses, as the first true condition will be the one executed. - For complex conditions, consider breaking them down into separate
CASEstatements or using derived tables for better readability. - Remember that
CASEstatements can impact query performance, especially when used with large datasets or complex conditions.