MySQL - IF Control Flow Function

In this tutorial we will learn about IF control flow function in MySQL.

The IF function

We use the IF function to check a given condition and then return some value based on a match.

IF syntax

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.

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

Example #2

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)