Conditional IF function

In the previous lesson, we looked at the CASE statement for implementing conditional logic in SQL. However, this is not the only mechanism by which it is possible to implement logic branching in a query. It's time to turn our attention to the IF function.

Syntax

MySQL
IF (conditional_expression,
    value_under_true_condition,
    value_on_false_condition);

The IF function looks at the truth of the conditional expression and, depending on this truth, returns either the value passed in the second argument or the third argument.

Examples

  • Simple example

    MySQL
    SELECT IF(10>20, "TRUE", "FALSE");
    
    IF(10>20, "TRUE", "FALSE")
    FALSE
  • Example of use with a real database

    MySQL
    SELECT id, price,
        IF(price >= 150, "Comfort class", "Economy class") AS category
        FROM Rooms
    
    idpricecategory
    1149Economy class
    2225Comfort class
    3150Comfort class
    489Economy class
    580Economy class
    6200Comfort class
    760Economy class
    879Economy class
    979Economy class
    10150Comfort class
    11135Economy class
    1285Economy class
    1389Economy class
    1485Economy class
    15120Economy class
    16140Economy class
    17215Comfort class
    18140Economy class
    1999Economy class
    20190Comfort class
    21299Comfort class
    22130Economy class
    2380Economy class
    24110Economy class
    25120Economy class
    2660Economy class
    2780Economy class
    28150Comfort class
    2944Economy class
    3030Comfort class
    3131Economy class
    3232Economy class
    3333Economy class
    3434Economy class
    3535Economy class
    3636Economy class
    3737Economy class
    3838Economy class
    3939Comfort class
    4040Economy class
    4141Economy class
    4242Economy class
    4343Economy class
    4444Economy class
    4545Comfort class
    4646Comfort class
    4747Economy class
    4848Economy class
    4949Economy class
    5050Economy class
  • IF functions can also be nested

    MySQL
    SELECT id, price,
        IF(price >= 200, "Business Class",
            IF(price >= 150,
                "Comfort class", "Economy class")) AS category
        FROM Rooms
    
    idpricecategory
    11Economy class
    22Business Class
    33Comfort class
    44Economy class
    55Economy class
    66Business Class
    77Economy class
    88Economy class
    99Economy class
    1010Comfort class
    1111Economy class
    1212Economy class
    1313Economy class
    1414Economy class
    1515Economy class
    1616Economy class
    1717Business Class
    1818Economy class
    1919Economy class
    2020Comfort class
    2121Business Class
    2222Economy class
    2323Economy class
    2424Economy class
    2525Economy class
    2626Economy class
    2727Economy class
    2828Comfort class
    2929Economy class
    3030Comfort class
    3131Economy class
    3232Economy class
    3333Economy class
    3434Economy class
    3535Economy class
    3636Economy class
    3737Economy class
    3838Economy class
    3939Comfort class
    4040Economy class
    4141Economy class
    4242Economy class
    4343Economy class
    4444Economy class
    4545Comfort class
    4646Comfort class
    4747Economy class
    4848Economy class
    4949Economy class
    5050Economy class

IFNULL and NULLIF functions

In addition to the IF function, SQL also has simpler, but less universal functions IFNULL and NULLIF, aimed at processing NULL values.

IFNULL syntax

MySQL
IFNULL(value, alternative_value);

The 'IFNULLfunction returns thevaluepassed by the first argument if it is not equal toNULL', otherwise it returns an `alternative_value'.

Examples with the IFNULL function

  • If the first argument is not NULL, then it will be returned.

    MySQL
    SELECT IFNULL("SQL Academy", 'Alternative SQL Academy") AS sql_trainer;
    
    sql_trainer
    SQL Academy
  • If the first argument is NULL, the value passed by the second argument will be returned.

    MySQL
    SELECT IFNULL(NULL, 'Alternative SQL Academy") AS sql_trainer;
    
    sql_trainer
    Alternative SQL Academy

NULLIF syntax

MySQL
NULLIF(value_1, value_2);

The NULLIF function returns NULL if value_1 is equal to value_2', otherwise it returns value_1'.

Examples with the NULLIF function

  • If the value of the first argument is equal to the value of the second argument, NULL is returned.

    MySQL
    SELECT NULLIF("SQL Academy", "SQL Academy") AS sql_trainer;
    
    sql_trainer
    <NULL>
  • If the values of the first and second arguments are different, the value of the first argument is returned.

    MySQL
    SELECT NULLIF("SQL Academy", 'Alternative SQL Academy") AS sql_trainer;
    
    sql_trainer
    SQL Academy