Updated on 27 Jul 2021

When to Use the SQL CASE Statement

Martin Ganchev
Published on 27 Aug 2018 9 min read

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 download 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

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;

Consider the SQL CASE statement within this query.

case statment

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:

SELECT
    e.emp_no,
    e.first_name,
    e.last_name,
    CASE dm.emp_no
        WHEN 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;

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

IF

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;

gender

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.

IF vs 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”.

30000

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.

28147

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.

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: Integrating SQL and Tableau

Learn data science with industry experts

Try For Free
Martin Ganchev

Instructor at 365 Data Science

Martin holds an MSc degree in Economic and Social Sciences from Bocconi University. His diverse academic and research experience combined with his friendly and explanatory approach to teaching have made him one of the most beloved instructors on our team. Some of the courses he has authored include: SQL, SQL + Tableau, SQL+Tableau+Python, Introduction to Python, Introduction to Jupyter, to name a few.

Learn data science with industry experts

Comprehensive training, exams, certificates. Find your dream job.

Start your career
413,000+ Reviews
Top