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, dive 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.
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.
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’.
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.
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.
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;
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;
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.
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.
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.
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.
Whereas if it’s false, the returned value will be the one written in the third place.
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.
IF vs CASE
Well, with the SQL CASE statement we can have multiple conditional expressions, while with IF we can have just one. 
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.
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.
Finally, if none of the conditions were met, the query would return '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.
***
Eager to hone your SQL skills? Enroll in our SQL course.
Next Tutorial: Integrating SQL and Tableau
