When to Use the SQL CASE Statement

SQL Tutorials 9 min read
sql case statement
Blog / SQL Tutorials / 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;

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

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

×
LAST CHANCE
Ready to Learn Data Science?
50% OFF