MySQL - CASE statement

In this tutorial we will learn about CASE statement in MySQL.

The CASE statement

We use the CASE statement to check if a given expression satisfies some given condition.

CASE syntax

CASE expression
    WHEN condition1 THEN result_1
    WHEN condition2 THEN result_2
    ...
    ELSE default_result
END

Where expression is optional and it is matched with the condition1, condition2, and so on. And if any condition satisfies the expression then that result is returned. Otherwise, if the ELSE result is present then, it is returned.

Table

In this tutorial we will be using the orders table.

Table: orders

mysql> SELECT * FROM orders;
+---------+------------+--------+-------------+---------------------+---------------------+
| orderid | employeeid | amount | orderstatus | lastmodified        | created             |
+---------+------------+--------+-------------+---------------------+---------------------+
|       1 | e03        |  15.00 | OPEN        | 2018-01-02 03:04:05 | 2018-01-02 03:04:05 |
|       2 | e01        |  25.50 | OPEN        | 2018-01-04 03:04:03 | 2018-01-04 03:04:03 |
|       3 | e05        | 100.70 | CLOSED      | 2018-02-02 03:03:04 | 2018-02-02 03:03:04 |
|       4 | e02        |  22.18 | OPEN        | 2018-01-02 03:04:05 | 2018-01-02 03:04:05 |
|       5 | e04        |   9.50 | CANCELLED   | 2018-01-04 03:04:03 | 2018-01-04 03:04:03 |
|       6 | e04        |  99.99 | OPEN        | 2018-02-02 03:03:04 | 2018-02-02 03:03:04 |
+---------+------------+--------+-------------+---------------------+---------------------+
6 rows in set (0.00 sec)

Example #1

In the following example we will output "Greater than 15" or "Less than 15" or "Equal to 15".

Note! In the given example we are not using the CASE expression.

mysql> SELECT 
    o.orderid,
    o.amount,
    CASE
        WHEN o.amount > 15 THEN 'Greater than 15'
        WHEN o.amount < 15 THEN 'Less than 15'
        ELSE 'Equal to 15'
    END AS result
FROM
    orders o;

+---------+--------+-----------------+
| orderid | amount | result          |
+---------+--------+-----------------+
|       1 |  15.00 | Equal to 15     |
|       2 |  25.50 | Greater than 15 |
|       3 | 100.70 | Greater than 15 |
|       4 |  22.18 | Greater than 15 |
|       5 |   9.50 | Less than 15    |
|       6 |  99.99 | Greater than 15 |
+---------+--------+-----------------+
6 rows in set (0.00 sec)

Example #02

In the following example we will show the status of the order.

Note! In the following example we are using the CASE expression.

mysql> SELECT 
    o.orderid,
    o.orderstatus,
    CASE o.orderstatus
        WHEN 'OPEN' THEN 'Order is in open state.'
        WHEN 'CLOSED' THEN 'Order is closed.'
        WHEN 'CANCELLED' THEN 'Order is cancelled.'
        ELSE 'Order is in unknown state.'
    END AS order_summary
FROM
    orders o;

+---------+-------------+-------------------------+
| orderid | orderstatus | order_summary           |
+---------+-------------+-------------------------+
|       1 | OPEN        | Order is in open state. |
|       2 | OPEN        | Order is in open state. |
|       3 | CLOSED      | Order is closed.        |
|       4 | OPEN        | Order is in open state. |
|       5 | CANCELLED   | Order is cancelled.     |
|       6 | OPEN        | Order is in open state. |
+---------+-------------+-------------------------+
6 rows in set (0.00 sec)

Example #3

In the following example we will get NULL if there is no match.

mysql> SELECT 
    o.orderid,
    o.orderstatus,
    CASE o.orderstatus
        WHEN 'OPEN' THEN 'Order is in open state.'
        WHEN 'CLOSED' THEN 'Order is closed.'
    END AS order_summary
FROM
    orders o;

+---------+-------------+-------------------------+
| orderid | orderstatus | order_summary           |
+---------+-------------+-------------------------+
|       1 | OPEN        | Order is in open state. |
|       2 | OPEN        | Order is in open state. |
|       3 | CLOSED      | Order is closed.        |
|       4 | OPEN        | Order is in open state. |
|       5 | CANCELLED   | NULL                    |
|       6 | OPEN        | Order is in open state. |
+---------+-------------+-------------------------+
6 rows in set (0.00 sec)