Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 26.9Mb
PDF (RPM) - 25.3Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.3Mb
HTML Download (RPM) - 5.3Mb
Man Pages (TGZ) - 157.6Kb
Man Pages (Zip) - 260.8Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  Functions and Operators  /  Control Flow Functions

12.4 Control Flow Functions

Table 12.6 Flow Control Operators

Name Description
CASE Case operator
IF() If/else construct
IFNULL() Null if/else construct
NULLIF() Return NULL if expr1 = expr2

  • CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

    CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

    The first CASE syntax returns the result for the first value=compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

    Note

    The syntax of the CASE expression described here differs slightly from that of the SQL CASE statement described in Section 13.6.5.1, “CASE Syntax”, for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

    The return type of a CASE expression result is the aggregated type of all result values.

    mysql> SELECT CASE 1 WHEN 1 THEN 'one'
        ->     WHEN 2 THEN 'two' ELSE 'more' END;
            -> 'one'
    mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
            -> 'true'
    mysql> SELECT CASE BINARY 'B'
        ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
            -> NULL
  • IF(expr1,expr2,expr3)

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

    Note

    There is also an IF statement, which differs from the IF() function described here. See Section 13.6.5.2, “IF Syntax”.

    If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.

    The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:

    • If expr2 or expr3 produce a string, the result is a string.

      If expr2 and expr3 are both strings, the result is case-sensitive if either string 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.

    mysql> SELECT IF(1>2,2,3);
            -> 3
    mysql> SELECT IF(1<2,'yes','no');
            -> 'yes'
    mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
            -> 'no'
  • IFNULL(expr1,expr2)

    If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

    mysql> SELECT IFNULL(1,0);
            -> 1
    mysql> SELECT IFNULL(NULL,10);
            -> 10
    mysql> SELECT IFNULL(1/0,10);
            -> 10
    mysql> SELECT IFNULL(1/0,'yes');
            -> 'yes'

    The default return type of IFNULL(expr1,expr2) is the more general of the two expressions, in the order STRING, REAL, or INTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:

    mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
    mysql> DESCRIBE tmp;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | test  | varbinary(4) | NO   |     |         |       |
    +-------+--------------+------+-----+---------+-------+

    In this example, the type of the test column is VARBINARY(4) (a string type).

  • NULLIF(expr1,expr2)

    Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

    The return value has the same type as the first argument.

    mysql> SELECT NULLIF(1,1);
            -> NULL
    mysql> SELECT NULLIF(1,2);
            -> 1
    Note

    MySQL evaluates expr1 twice if the arguments are not equal.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Joseph Edmonds on January 19, 2012
Note using the case syntax you can update many rows with one query. This can have a really dramatic postitive impact on performance if you have a large number of updates to do.

For a nice PHP function to handle this see here:
http://www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/

  Posted by Mike Jorgenstam on February 28, 2014
This does not work to validate that all actually fall within as some can have > 1 five while still being within 20 sum and then discards second statement after OR

Thus, MySQL reads the passed if clause in the statement from LEFT TO RIGHT and not from RIGHT TO LEFT as some say...

-- This fails
IF ((@r7q0+@r7q1+@r7q2+@r7q3+@r7q4+@r7q5) < 20) OR
OR (@r7q5 > 1) THEN
SET @res = 0;
END IF;

-- This works
IF ((@r7q6+@r7q7+@r7q8+@r7q9+@r7q10) > 0) OR (@r7q5 > 1) THEN
SET @res = 0;
END IF;

-- This would work in some cases then
IF (@r7q5 > 1) OR
((@r7q0+@r7q1+@r7q2+@r7q3+@r7q4+@r7q5) < 20) THEN
SET @res = 0;
END IF;

Sign Up Login You must be logged in to post a comment.