How do I Add Case to a MySQL Query?

In MySQL, the CASE statement describes the ways of handling a loop to execute a certain set of conditions and return the case matched using IF-ELSE. It’s a control statement that validates a set of conditions. If there’s any case found TRUE, then it returns the value. If no case matches, it returns the ELSE value. If no ELSE value was defined, then it returns NULL.

This guide will dive into the CASE statement in MySQL.

The CASE statement in MySQL

If you have previous programming experience in any of the popular programming languages, then the CASE statement is definitely a familiar topic. The structure is as follows.

CASE
WHEN THEN
WHEN THEN
ELSE
END

Let’s have a quick breakdown of each of the parts.

<condition_n>: The conditions required to evaluate the CASE statement.
<result_n>: The result of the respective case match.
<else_result>: The result when no matching case was found. If not defined, then CASE returns NULL.

If you look closely, the structure is almost exactly what you’d find in a typical Java if-else logic structure.

Using the CASE statement

Basic usage
To demonstrate the application of CASE, we’re going to use a dedicated MySQL database. For ease of use, I’ll be using phpMyAdmin to access and manage databases. Learn more about installing and configuring phpMyAdmin and MySQL on Ubuntu.

I’ve created an empty database hello_world.

Our sample database will contain info about students. Create a new table named Students.

$ CREATE TABLE Students(ID int, Name varchar(255), State varchar(255), City varchar(255));

Now, insert some sample input into the table.

$ INSERT INTO Students (ID, Name, State, City) VALUES ('1', 'Adrien', 'Arizona', 'Prescott');
$ INSERT INTO Students (ID, Name, State, City) VALUES ('2', 'Alisha', 'Nebraska', 'Omaha');
$ INSERT INTO Students (ID, Name, State, City) VALUES ('3', 'Brenda', 'Michigan', 'Detroit');
$ INSERT INTO Students (ID, Name, State, City) VALUES ('4', 'Thompson', 'New York', '');
$ INSERT INTO Students (ID, Name, State, City) VALUES ('5', 'David', '', 'Portland');

The final table should look like this.

Have a look at the following query and its output.

SELECT Name, State, City FROM Students ORDER BY (
CASE
WHEN State is NULL THEN City
ELSE State
END);

Using CASE statement with aggregate function
In the next example, we’ll create a new database table containing sales information. From that info, we’ll figure out the count of orders by the status of the order. To do so, we’ll also be using the SUM() function in MySQL. The following guide dives deep into the SUM() function in MySQL.

Create a new table Orders.

$ CREATE TABLE Orders (order_ID int, sales_ID int, status varchar(255));

Let’s populate the table with some sample data.

$ INSERT INTO Orders (order_ID, sales_ID, status) VALUES ('5', '10', 'processing');
$ INSERT INTO Orders (order_ID, sales_ID, status) VALUES ('8', '244', 'success');
$ INSERT INTO Orders (order_ID, sales_ID, status) VALUES ('4', '274', 'processing');
$ INSERT INTO Orders (order_ID, sales_ID, status) VALUES ('80', '452', 'success');
$ INSERT INTO Orders (order_ID, sales_ID, status) VALUES ('10', '10000', 'processing');

The following code will count the instances of success and processing.

SELECT
SUM(CASE
WHEN status = 'success' THEN 1
ELSE 0
END) AS 'Success count',
SUM(CASE
WHEN status = 'processing' THEN 1
ELSE 0
END) AS 'processing',
COUNT(*) AS 'Sum total'
FROM Orders;

As the output shows, it shows a sum of all the orders according to their status. First, the CASE statements execute and return 1 each time it’s matched with the respective order status. Finally, the SUM() function calculates the total number of orders.

Final thought

In this guide, we explored the usage of the CASE statement in MySQL. In short, it’s a logic statement that allows performing IF-ELSE logic to the SQL queries in an efficient manner. We showcased how to implement it in various situations. Compared to using multiple IF-ELSE statements, CASE makes the code more readable and efficient.

MySQL is rich in powerful features. To learn more, check out the following guides on schema, the DATEDIFF() function, working with substring, etc.

Happy computing!

How do I Add Case to a MySQL Query?

The CASE statement in MySQL

Using the CASE statement

Final thought

About the author

Sidratul Muntaha