MySQL
In this tutorial we will learn about IF control flow function in MySQL.
We use the IF function to check a given condition and then return some value based on a match.
IF (condition, result_for_true, result_for_false)
If the condition
is satisfied then result_for_true
is returned. Else result_for_false
is returned.
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)
In the following example we will check if the amount is 'Greater than 15' or 'Less than or Equal to 15'.
mysql> SELECT
o.orderid,
IF(o.amount > 15,
'Greater than 15',
'Less than or Equal to 15') AS result
FROM
orders o;
+---------+--------------------------+
| orderid | result |
+---------+--------------------------+
| 1 | Less than or Equal to 15 |
| 2 | Greater than 15 |
| 3 | Greater than 15 |
| 4 | Greater than 15 |
| 5 | Less than or Equal to 15 |
| 6 | Greater than 15 |
+---------+--------------------------+
6 rows in set (0.00 sec)
In the following example we will add another IF function to check if the amount is 'Greater than 15' or 'Less than 15' or 'Equal to 15'.
mysql> SELECT
o.orderid,
IF(o.amount > 15,
'Greater than 15',
IF(o.amount < 15,
'Less than 15',
'Equal to 15')) AS result
FROM
orders o;
+---------+-----------------+
| orderid | result |
+---------+-----------------+
| 1 | Equal to 15 |
| 2 | Greater than 15 |
| 3 | Greater than 15 |
| 4 | Greater than 15 |
| 5 | Less than 15 |
| 6 | Greater than 15 |
+---------+-----------------+
6 rows in set (0.00 sec)
ADVERTISEMENT