MySQL - CASE statement

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.

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)