How to Use CASE Statement in MySQL

Last updated on June 25th, 2024 at 06:31 am

Sometimes you may need to use conditional values in your SQL queries. You may need these values to be generated based on one or more conditions. In such cases, you can use CASE or IF-ELSE statements. 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. We will also briefly compare it with IF-ELSE statement.

Table of Contents

What is CASE Statement

MySQL CASE statement evaluates a set of conditions and returns an output when the first condition is met. If no condition is met, then it returns the value specified in else statement. If no else statement is mentioned then it returns null. Here is the syntax for MySQL Case statement.

select 
case 
    when condition1 then value1
    when condition2 then value2
    ...
    else result
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.

Here are some of the key benefits of CASE statement:

They can be used in SELECT, WHERE and ORDER BY clauses of your SQL queries.
They allow you to implement complex logical conditions reducing dependency on outside programming.
They can accommodate a large number of conditions and results in a compact manner.
You don’t need to do nesting, like in case of IF-ELSE statements.

CASE vs IF ELSE

Both CASE and IF ELSE statements evaluate a sequence of conditions and stop when there is first match. We have already mentioned the syntax of CASE statement above. Here is the syntax of IF statement.

IF(condition, value_if_true, value_if_false)

The main advantage of using CASE is that it is more flexible than IF statement. It can handle more output branches whereas IF statement supports only 2 branches, one when the condition is true and the other when it is false. Here is an example where single CASE statement has 3 output branches.

select case 
       when username = 'jim' then 'high' 
       when username = 'john' then 'medium' 
       else 'low' 
       end as height

CASE statement allows you to build a switch like mechanism where you have a large number of different conditions and return a different output for different condition. Here is an example to illustrate it.

select case username 
       when 'john' then 'high' 
       when 'jim' then 'medium' 
       else 'low' 
       end as height

Implementing both the above statements will require you to do nested IF-ELSE statements.

Also it is more intuitive than IF. Look at the two equivalent statements using IF and CASE statement.

select if(username = 'john', 'high', 'low') as grades
select case when username = 'john' then 'high' else 'low' end as grades

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 |
+------+------------+--------+

Example 1

Here’s the SQL query to group the amount values into 3 buckets – less than equal to 100, 100-300, and more than 300. In this query, CASE will evaluate the amount column for each row, for each of the ‘when’ conditions, one after the other, and assign the text after ‘then’ part. For each row, it will stop evaluating after the first match.

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       |
+------+------------+------------------------+

Example 2

Please note, if none of the case statements are satisfied for a value, then the CASE statement will return NULL. Here there is no case matching amount=100 so it returns NULL.

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 |
+------+------------+------------------+

If you do not want NULL in your output, then include an ELSE statement after all WHEN statements.

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'
          else 'not available'
     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 | Not available    |
|    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 is useful for creating frequency distributions and grouping values.

Example 3

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>;

Here the WHERE clause will first be applied to filter required rows and only then CASE will be evaluated. This is useful if you have a large table and do not want to apply CASE on all rows. It saves a lot of time.

Conclusion

In this article, we have learnt what is CASE in MySQL, how to use it, differences between CASE and IF-ELSE. CASE is a versatile feature that allows you to easily generate conditional values in MySQL and other databases. It is very flexible since you can do obtain multi-pronged result for your column. It is more compact and intuitive than using IF statement, which requires nesting for complex use cases.

Using 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.