How to Use CASE Statement in MySQL

MySQL Case statement allows you to check a value for multiple conditions in an SQL query. In this article we will look at how to use case statement in MySQL.

How to Write Case Statement in MySQL

Here is the syntax for MySQL Case statement.

select 
case 
    when condition1 then value1
    when condition2 then value2
    ...
end,
column2, column3, ...
from table_name

In the above query, you need to specify the table name. Also you need to mention each condition that you want to check a given column for, within case… end statement. You also need to mention the value to be assigned to the column if each condition is true, that is, for each case.

MySQL Case examples

Let us look at some examples of MySQL Case function below.

Let us say you have the following table sales(id, order_date, amount).

mysql> create table sales(id int, order_date date, amount int);

mysql> insert into sales(id, order_date, amount)
     values(1, '2021-01-01', 150),
     (1, '2021-01-02', 250),
     (1, '2021-01-03', 100),
     (1, '2021-01-04', 150),
     (1, '2021-01-05', 350);

mysql> select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2021-01-01 |    150 |
|    1 | 2021-01-02 |    250 |
|    1 | 2021-01-03 |    100 |
|    1 | 2021-01-04 |    150 |
|    1 | 2021-01-05 |    350 |
+------+------------+--------+

Bonus read: How to Query JSON column in MySQL

Here’s the SQL query to group the amount values into 3 buckets – less than equal to 100, 100-300, and more than 300.

mysql> select id, order_date,
     case when amount<=100 then 'less than equal to 100'
          when amount>100 and amount<300 then '101 to 300'
          when amount>=300 then 'greater than 300'
     end as bucket
     from sales;
+------+------------+------------------------+
| id   | order_date | bucket                 |
+------+------------+------------------------+
|    1 | 2021-01-01 | 101 to 300             |
|    1 | 2021-01-02 | 101 to 300             |
|    1 | 2021-01-03 | less than equal to 100 |
|    1 | 2021-01-04 | 101 to 300             |
|    1 | 2021-01-05 | greater than 300       |
+------+------------+------------------------+

Bonus read : How to Avoid inserting duplicate records in MySQL

Please note, if none of the case statements are satisfied for a value, then the CASE statement will return NULL. Here’s an example

mysql> select id, order_date,
     case when amount<100 then 'less than 100'
          when amount>100 and amount<300 then '100 to 300'
     when amount>300 then 'greater than 300'
     end as bucket
     from sales;
+------+------------+------------------+
| id   | order_date | bucket           |
+------+------------+------------------+
|    1 | 2021-01-01 | 100 to 300       |
|    1 | 2021-01-02 | 100 to 300       |
|    1 | 2021-01-03 | NULL             |
|    1 | 2021-01-04 | 100 to 300       |
|    1 | 2021-01-05 | greater than 300 |
+------+------------+------------------+

In the above example, the CASE statement returns NULL for 100, since it does not satisfy any of the conditions.

MySQL Case statement is useful for creating frequency distributions and grouping values.

You can also use WHERE condition to apply case statement on a subset of rows.

select id, order_date,
     case when amount<=100 then 'less than equal to 100'
          when amount>100 and amount<300 then '101 to 300'
          when amount>=300 then 'greater than 300'
     end as bucket
     from sales
     WHERE <condition>;

MySQL Case in Ubiq

Ubiq Reporting tool supports all the above SQL queries and makes it easy to visualize SQL results in different ways. Here is the CASE SQL query mentioned above, in Ubiq.