When you’re working with a database, you may want to run an
if/then operation in your query. For example, you may want to go through a list of employees and change their probation status if they have been working with you for over a year. Or you may want to go through a list of players on a leaderboard and mark them as a winner if they rank in the top three.
In order to run these operations in
SQL, you have to use the
CASE statement. The
SQL CASE statement allows you to run
if/then operations, similar to how you can run
if/then procedures in Microsoft Excel.
In this guide, we’re going to break down the basics of the
SQL CASE statement, and discuss how you can use it in your queries. We’ll also discuss how to use multiple
SQL CASE statements, and explore how
CASE can be used with aggregate functions.
To retrieve information from a database, you need to write a query. Queries almost always start with the
SELECT statement, which is used to tell the database which columns should be returned by the query. Queries also usually include a
FROM clause, which tells the database which table the operation will search.
Here’s the syntax for a query in
SELECT column_name FROM table_name WHERE conditions_are_met;
Let’s use an example to illustrate this in action. Here is a query that will return the names of all employees in our
SELECT name FROM employees;
Our query returns the result:
If we wanted to retrieve multiple columns, we could do so by separating their names with a column. Or if we wanted to gather information about every column, we could use the asterisk (
*) operator, which represents all the columns in an
In addition, if we want to filter records based on a specific set of conditions, we can use the
WHERE clause. Here’s an example of a query that finds the names of all employees who are based in the Albany branch of a company:
SELECT name FROM employees WHERE branch = 'Albany';
Here is the result of our query:
These are all relatively simple queries. But what if we wanted to perform an
if/then operation when we’re running a query? That’s where the
CASE statement can be helpful.
CASE statement can be used in
SQL to define
if/then logic in our code. For example, if we wanted to give every employee a raise who has worked with our business for five or more years, we may want to use the
Here’s the syntax for the
SQL CASE statement:
SELECT column1_name CASE WHEN column2_name = 'X' THEN 'Y' ELSE NULL END AS column3_name FROM table_name;
There is a lot going on in this query, so let’s use an example to illustrate how it works. Let’s say that we want to give every employee with more than five
employee of the month awards a $200 raise. Here’s an
SQL statement that could accomplish that goal:
SELECT name, CASE WHEN employee_month_awards > 5 THEN 200 ELSE NULL END AS pending_raise FROM employees;
Our query returns the following from our searched case expression:
Let’s break this down. The
CASE statement checks each record and evaluates whether the conditional statement,
employee month awards > 5, is true. If the conditional statement is true, the value
200 will be printed in the
pending_raise column. Where the conditional statement is false, a null value remains.
Finally, our query returns the list of employee names, as well as the pending raises of those employees.
It’s important to note that the
SQL CASE statement does not add a new column to our table. Rather, it creates a column in our
SELECT query output, so that we can see who is eligible for a raise.
In addition, if we wanted everyone who was not eligible for a raise to be given a
pending raise, we could specify
NULL in our
ELSE statement. We could also use an
ORDER BY clause to order our statement if we wanted to see our data in a specific order.
SQL CASE and Multiple Conditions
CASE statement can be used multiple times in the same query. If we wanted to give every employee who has three or more awards a $50 raise, and everyone who has five or more awards a $200 raise, we could use the following statement:
SELECT name, CASE WHEN employee_month_awards > 5 THEN 200 WHEN employee_month_awards > 3 THEN 50 ELSE 0 END AS pending_raise FROM employees;
The output from our query is as follows:
In our example, the
CASE statements will be evaluated in the order in which they are written.
So, our query first checks for people who have more than five awards, and sets their pending raise to
200. Then, our query checks for people who have more than three awards, and sets their pending raise to
50. Finally, if an employee doesn’t meet any of the criteria, their pending raise will be set to
However, this code could be more efficient. Instead of writing statements in a certain order so that our program works, we should write statements that do not overlap. Here’s an example of a query that works in the same way as above, but uses
AND statements to check the number of awards an employee has earned:
SELECT name, CASE WHEN employee_month_awards >= 3 AND employee_month_awards <= 5 THEN 50 WHEN employee_month_awards > 5 THEN 200 ELSE 0 END AS pending_raise FROM employees;
Our query returns the same as our above query. However, this one doesn’t depend on the order of
CASE statements, which means we are less likely to make a mistake with a misplaced statement.
SQL CASE and Aggregate Functions
You can also use
CASE with an aggregate function. This can be useful if you only want to count rows that meet a certain condition. For example, if you want to find out how many employees have earned a bonus of $200, you could use
CASE with an aggregate function.
Here’s the syntax for using
CASE with an aggregate function:
SELECT column1_name CASE WHEN column2_name = 'X' THEN 'Y' ELSE NULL END AS column3_name, COUNT(1) AS count FROM table_name GROUP BY column3_name;
Let’s use an example to illustrate how this works. Let’s say that we want to find out how many employees are eligible for a bonus of $50 or over. We could use the following query to get this information:
SELECT CASE WHEN employee_month_awards >= 3 AND employee_month_awards <= 5 THEN 50 WHEN employee_month_awards > 5 THEN 200 ELSE 0 END AS pending_raise, COUNT(1) AS count FROM employees GROUP BY pending_raise;
Our query returns the following:
As you can see, our query has returned a list of the pending raises earned by employees, as well as the number of each type of raise that employees are due. In this case, three employees are due a $50 raise, three employees are due no raise, and two employees are due a $200 raise.
In this tutorial, we have broken down the basics of the
CASE statement and discussed how it can be used to implement
if/then logic in our queries. We also discussed how
CASE can be used with multiple conditions and aggregate functions.
As a reminder, every simple
CASE expression should follow these rules:
CASEstatement should be in the
CASEstatement should include
ELSEclauses can be used optionally;
- Conditional statements, such as
OR, can be used in a
CASEquery between the
Now you’re equipped with the knowledge you need to use
CASE statements like an