MySQL has an IF() function that provides a convenient way to perform a simple “IF/ELSE” operation.

It works similar to a basic IF/ELSE statement, in that it allows us to check for a condition, and return a different result depending on whether it’s true or not.

More specifically, if the first argument to the IF() function is true, the second argument is returned. If it’s not true, the third argument is returned.

Syntax

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 IS NOT NULL), IF() returns expr2. Otherwise, it returns expr3.

Return Type

• If expr2 or expr3 produce a string, the result is a string. If expr2 and expr3 are both strings and either string is case-sensitive, the result is case-sensitive.
• If expr2 or expr3 produce a floating-point value, the result is a floating-point value.
• If expr2 or expr3 produce an integer, the result is an integer.

Example

Here’s a simple example to demonstrate how it works:

SELECT IF( 1 > 2, 'Yes', 'No' );

Result:

No

Here, we checked whether or not 1 is greater than 2. It’s not, and so the third argument was returned.

Here’s what happens when the first expression is true:

SELECT IF( 2 > 1, 'Yes', 'No' );

Result:

Yes

Database Example

Here’s an example that uses the IF() function when querying a database:

SELECT 
    Name,
    Population,
    IF( Population > 10000000, 'Big', 'Small' ) AS "Big/Small"
FROM Country
ORDER BY Name ASC
LIMIT 10;

Result:

+---------------------+------------+-----------+
| Name                | Population | Big/Small |
+---------------------+------------+-----------+
| Afghanistan         |   22720000 | Big       |
| Albania             |    3401200 | Small     |
| Algeria             |   31471000 | Big       |
| American Samoa      |      68000 | Small     |
| Andorra             |      78000 | Small     |
| Angola              |   12878000 | Big       |
| Anguilla            |       8000 | Small     |
| Antarctica          |          0 | Small     |
| Antigua and Barbuda |      68000 | Small     |
| Argentina           |   37032000 | Big       |
+---------------------+------------+-----------+

Nested IF() Functions

It’s possible to nest IF() functions in order to provide for more than a binary outcome.

For example:

SELECT 
    Name,
    Population,
    IF( 
        Population > 10000000, 
        IF( Population > 100000000, 'REALLY Big', 'Big' ), 
        'Small' 
        ) AS "Size"
FROM Country
WHERE Region = 'Southern and Central Asia'
ORDER BY Population DESC;

Result:

+--------------+------------+------------+
| Name         | Population | Size       |
+--------------+------------+------------+
| India        | 1013662000 | REALLY Big |
| Pakistan     |  156483000 | REALLY Big |
| Bangladesh   |  129155000 | REALLY Big |
| Iran         |   67702000 | Big        |
| Uzbekistan   |   24318000 | Big        |
| Nepal        |   23930000 | Big        |
| Afghanistan  |   22720000 | Big        |
| Sri Lanka    |   18827000 | Big        |
| Kazakstan    |   16223000 | Big        |
| Tajikistan   |    6188000 | Small      |
| Kyrgyzstan   |    4699000 | Small      |
| Turkmenistan |    4459000 | Small      |
| Bhutan       |    2124000 | Small      |
| Maldives     |     286000 | Small      |
+--------------+------------+------------+

Nulls and Zeros

If the first expression is either NULL or 0, then it’s false, and the second value is returned:

SELECT
    IF( 1, 'True', 'False' ) AS "1",
    IF( null, 'True', 'False' ) AS "Null",
    IF( 0, 'True', 'False' ) AS "Zero";

Result:

+------+-------+-------+
| 1    | Null  | Zero  |
+------+-------+-------+
| True | False | False |
+------+-------+-------+

Here, the first column is true because it resolves to 1. The other two columns return the second argument, because their first argument was null and 0 respectively.

Here’s a database example:

SELECT 
    Name,
    GNPOld,
    IF( GNPOld, GNPOld, 'None' )
FROM Country
ORDER BY Name ASC
LIMIT 10;

Result:

+---------------------+-----------+------------------------------+
| Name                | GNPOld    | IF( GNPOld, GNPOld, 'None' ) |
+---------------------+-----------+------------------------------+
| Afghanistan         |      NULL | None                         |
| Albania             |   2500.00 | 2500.00                      |
| Algeria             |  46966.00 | 46966.00                     |
| American Samoa      |      NULL | None                         |
| Andorra             |      NULL | None                         |
| Angola              |   7984.00 | 7984.00                      |
| Anguilla            |      NULL | None                         |
| Antarctica          |      NULL | None                         |
| Antigua and Barbuda |    584.00 | 584.00                       |
| Argentina           | 323310.00 | 323310.00                    |
+---------------------+-----------+------------------------------+

Although in this case the same outcome could have been achieved with slightly less code using the IFNULL() function or even the COALESCE() function.