SQL Case

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.

Query Refresher

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 SQL:

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 employees table:

SELECT name FROM employees;

Our query returns the result:

(8 rows)

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 SQL table.

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:

(2 rows)

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 SQL CASE statement can be helpful.

SQL CASE

The 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 CASE statement.

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:

(8 rows)

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 0 pending raise, we could specify 0 instead of 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

The 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:

(8 rows)

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 0.

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match

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.

Conclusion

In this tutorial, we have broken down the basics of the SQL server 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:

• The CASE statement should be in the SELECT clause;
• The CASE statement should include WHEN, THEN, and END components;
• Multiple WHEN statements and ELSE clauses can be used optionally;
• Conditional statements, such as AND or OR, can be used in a CASE query between the WHEN and THEN clauses.

Now you’re equipped with the knowledge you need to use CASE statements like an SQL professional!