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.
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
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.
Now, insert some sample input into the table.
$ 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.
WHEN State is NULL THEN City
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.
Let’s populate the table with some sample data.
$ 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.
WHEN status = 'success' THEN 1
END) AS 'Success count',
WHEN status = 'processing' THEN 1
END) AS 'processing',
COUNT(*) AS 'Sum total'
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.
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.