Data Science PR
When to Use the SQL CASE Statement,sql if statement,sql case when multiple values,sql case statement in where clause multiple values,nested case statement in sql,multiple case when sql,case statement with multiple conditions in sql server,assign value using case statement in sql,sql case when null,

When to Use the SQL CASE Statement

In this tutorial, we will be talking about a conditional construct called the SQL CASE statement.

Side note: If you want to read about another advanced SQL topic, don’t think twice before diving into our tutorial on MySQL indexes.

So, MySQL is a rich language and there are many ways a condition can be expressed.

For instance, you may want to retrieve a type of query output in case a specific condition has been satisfied And another type of output in case it has not been satisfied.

output one output two, SQL case statement

As an example, remember that one way to apply such technique is through the COALESCE or the IFNULL functions.

However, let’s focus on the SQL CASE statement. It is used within a SELECT statement when we want to return a specific value, based on some condition.

The Syntax

Its syntax can vary depending on what we want to show.

select, sql case statement

Let’s see some real-life examples:

As we know, in our ‘employees’ table we have a column called ‘gender’, filled with data of the ENUM type. It contains the values ‘M’ and ‘F’.

employees e gender, sql case statement

But what if we want to return some more meaningful values instead?

Side note: If you haven’t downloaded the ‘employees’ database that we will be using, make sure you doawnload it here.

Well, here’s how the SQL CASE statement can help.

Providing Examples

In this example, when the value of the column is ‘M’, we will return ‘Male’; and if it’s ‘F’ – ‘Female’. As simple as that.

m = male f = female, sql case statement

Therefore, observe how the syntax of the CASE construct starts with the keyword CASE followed by WHEN and a conditional expression containing the word THEN. After that, we have ELSE as a final expression if all conditions mentioned so far turn out false.

Furthermore, END is an obligatory part of the syntax. As it name suggests, it shows where the CASE statement will terminate.

case when else end then, sql case statement
data science training

Running the Code

Let’s see what happens after we run the following query:

SELECT emp_no, first_name, last_name, CASE WHEN gender = 'M' THEN 'Male' ELSE 'Female' END AS gender FROM employees;
Gender, sql case statement

Is There Another Way to Write an SQL CASE Statement?

If we write the following code, we should retrieve the same output:

SELECT emp_no, first_name, last_name, CASE gender WHEN 'M' THEN 'Male' ELSE 'Female' END AS gender FROM employees;
select gender, sql case statement

As shown in the picture above, we achieved what we wanted.

This means that we can obtain the same exact result by putting the name of the column once – right after the word CASE. Then, we should write the corresponding value after the WHEN keyword, without using ’=’.

Times You Cannot Use it

We must say that this technique wouldn’t work in all cases, though. For instance, consider the following query:

SELECT e.emp_no, e.first_name, e.last_name, CASE WHEN dm.emp_no IS NOT NULL THEN 'Manager' ELSE 'Employee' END AS is_manager FROM employees e LEFT JOIN dept_manager dm ON dm.emp_no = e.emp_no WHERE e.emp_no > 109990;
case statment

Consider the SQL CASE statement within this query.

It says that if ‘dm.emp_no’ is not null, then SQL will return the value ‘Manager’. Else the returned value will be ‘Employee’.

And, as we run this query, that’s exactly the output we obtain, as you can see in the picture below.

is manager, sql case statement

Why It Can’t Be Used?

Now, just as an exercise, try rewriting and executing the same query by removing ‘dm.emp_no’ from the line with the WHEN keyword and placing it right after CASE. This is what the code should look like:





               CASE dm.emp_no

                              WHEN NOT NULL THEN ‘Manager’

                              ELSE ‘Employee’

               END AS is_manager


               employees e

                              LEFT JOIN

               dept_manager dm ON dm.emp_no = e.emp_no


               e.emp_no > 109990;

The Problem with IS NULL and IS NOT NULL

As shown in the picture below, the query didn’t return the correct result. All values in the last column are ‘Employee’ only.

is manager

However, we wanted to obtain the value ‘Manager’ if the employee is also a manager. At the same time, using this syntax, the query just returns ‘Employee’.

This is happening because IS NULL and IS NOT NULL are not values that something can be compared to. So, the correct way of writing this construction is CASE WHEN and then putting the conditional expression containing IS NULL or IS NOT NULL.

The IF Statement


What you see in the picture above is the IF construct. The first member within the parentheses is the condition which we want to be true. If it is true, then the returned value will be the second expression of this construct.

value if true

Whereas if it’s false, the returned value will be the one written in the third place.

value if false

Executing The Query

So, let’s run the following query:

SELECT emp_no, first_name, last_name, IF(gender = ‘M’, ‘Male’, ‘Female’) AS gender FROM employees;

Looking at the picture above, we can observe that this query returns the same result as the one where we showed how to obtain the values ‘Male’ or ‘Female’ using the SQL CASE statement. And if you compare the two queries, you could infer they look almost the same.

However, the IF statement has some limitations compared to CASE.


Well, with the SQL CASE statement we can have multiple conditional expressions, while with IF we can have just one.

IF vs Case

For example, let’s take a look at the following query:

SELECT dm.emp_no, e.first_name, e.last_name, MAX(s.salary) - MIN(s.salary) AS salary_difference, CASE WHEN MAX(s.salary) - MIN(s.salary) > 30000 THEN 'Salary was raised by more than $30,000' WHEN MAX(s.salary) - MIN(s.salary) BETWEEN 20000 and 30000 THEN 'Salary was raised by more than $20,000 and less than $30,000' ELSE 'Salary was raised by less than $20,000' END AS salary_increase FROM dept_manager dm JOIN employees e ON e.emp_no = dm.emp_no JOIN salaries s ON s.emp_no = dm.emp_no GROUP BY s.emp_no;

How to Work with Several WHEN Expressions?

By executing it, we can obtain the increase in the salaries of all department managers, based on some conditions. By using multiple WHEN expressions, we can return more than two values in the ‘salary_increase’ column.

when when then then

The Conditions

If a certain manager has a raise higher than $30,000, we will return “Salary was raised by more than $30,000”.


If, instead, it’s between $20,000 and $30,000, we would like to see ‘Salary was raised by more than $20,000 but less than $30,000’ displayed.

between 20000 and 30000 then

Finally, if none of the conditions were met, the query would return ‘Salary was raised by less than $20,000’.

Else salary was raised by less than $20,000

Let’s run the statement to see if we have worked correctly.


So, this was a brilliant example of a query where the output cannot be obtained with a simple IF statement.

When to Use the SQL CASE Statement?

To sum up, now you should be able to work with the CASE statement with ease. Sometimes, you can write less code, but only if you are not using IS NULL or IS NOT NULL. Moreover, we introduced the IF statement, which can substitute the SQL CASE statement every now and then. Furthermore, after reading this tutorial, using multiple WHEN expressions should be a piece of cake.

Now, if you want to take your coding experience to another level, you might be interested to learn how to combine SQL with Tableau.

This tutorial first appeared on:

Data Science PR

5 1 vote
Article Rating
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Would love your thoughts, please comment.x