The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL. The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause.

CASE Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Demo Database

Below is a selection from the “OrderDetails” table in the Northwind sample database:

MySQL CASE Examples

The following SQL goes through conditions and returns a value when the first condition is met:

Example

SELECT OrderID, Quantity,<br>CASE<br>    WHEN Quantity > 30 THEN 'The quantity is greater than 30'<br>    WHEN Quantity = 30 THEN 'The quantity is 30'<br>    ELSE 'The quantity is under 30'<br>END AS QuantityText<br>FROM OrderDetails;

The following SQL will order the customers by City. However, if City is NULL, then order by Country:

Example

SELECT&nbsp;CustomerName, City, Country<br>FROM&nbsp;Customers<br>ORDER&nbsp;BY<br>(CASE<br>&nbsp;&nbsp;&nbsp;&nbsp;WHEN&nbsp;City&nbsp;IS&nbsp;NULL&nbsp;THEN&nbsp;Country<br>&nbsp;&nbsp;&nbsp;&nbsp;ELSE&nbsp;City<br>END);

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

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

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 Expression

MySQL CASE expression is a part of the control flow function that provides us to write an if-else or if-then-else logic to a query. This expression can be used anywhere that uses a valid program or query, such as SELECT, WHERE, ORDER BY clause, etc. The CASE expression validates various conditions and returns the result when the first condition is true. Once the condition is met, it stops traversing and gives the output. If it will not find any condition true, it executes the else block. When the else block is not found, it returns a NULL value. The main goal of MySQL CASE statement is to deal with multiple IF statements in the SELECT clause.

We can use the CASE statement in two ways, which are as follows:

1. Simple CASE statement:

The first method is to take a value and matches it with the given statement, as shown below.

Syntax

It returns the result when the first compare_value comparison becomes true. Otherwise, it will return the else clause.

Example

mysql> <strong>SELECT</strong> CASE 1 <strong>WHEN</strong> 1 <strong>THEN</strong> 'one' <strong>WHEN</strong> 2 <strong>THEN</strong> 'two' <strong>ELSE</strong> 'more' <strong>END</strong>;  

Output

After the successful execution of the above command, we will see the following output.

2. Searched CASE statement:

The second method is to consider a search_condition in the WHEN clauses, and if it finds, return the result in the corresponding THEN clause. Otherwise, it will return the else clause. If else clause is not specified, it will return a NULL value.

Syntax

Example

mysql> <strong>SELECT</strong> CASE <strong>BINARY</strong> 'B' <strong>WHEN</strong> 'a' <strong>THEN</strong> 1 <strong>WHEN</strong> 'b' <strong>THEN</strong> 2 <strong>END</strong>;  

Output

Return Type

The CASE expression returns the result depending on the context where it is used.

• If it is used in the string context, it returns the string result.
• If it is used in a numeric context, it returns the integer, float, decimal value.

Conclusion

In this tutorial, we learned about CASE Statement in MySQL which is used to evaluate a given condition and set the resultant value to be displayed along with the query results. CASE is usually used with SELECT commands to fetch the required result set. We also learned how MySQL CASE can be used along with UPDATE commands to update an existing column in a table depending upon the values of any other existing column.