(mysql.info.gz) Control flow functions

Info Catalog

(mysql.info.gz) Non-typed Operators

(mysql.info.gz) Functions

(mysql.info.gz) String functions

 
 12.2 Control Flow Functions
 ===========================
 
 `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 version returns the RESULT where `VALUE=COMPARE-VALUE'.
      The second version returns the result for the first condition that
      is true. If there was no matching result value, the result after
      `ELSE' is returned, or `NULL' if there is no `ELSE' part.
 
           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
 
      The type of the return value (`INTEGER', `DOUBLE', or `STRING') is
      the same as the type of the first returned value (the expression
      after the first `THEN').
 
      `CASE' was added in MySQL 3.23.3.
 
 `IF(EXPR1,EXPR2,EXPR3)'
      If EXPR1 is TRUE (`EXPR1 <> 0' and `EXPR1 <> NULL') then `IF()'
      returns EXPR2, else it returns EXPR3.  `IF()' returns a numeric or
      string value, depending on the context in which it is used.
 
           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'
 
      If only one of `expr2' or `expr3' is explicitly `NULL', the result
      type of the `IF()' function is the type of non-`NULL' expression.
      (This behavior is new in MySQL 4.0.3.)
 
      `expr1' is evaluated as an integer value, which means that if you
      are testing floating-point or string values, you should do so
      using a comparison operation.
 
           mysql> SELECT IF(0.1,1,0);
                   -> 0
           mysql> SELECT IF(0.1<>0,1,0);
                   -> 1
 
      In the first case shown, `IF(0.1)' returns `0' because `0.1' is
      converted to an integer value, resulting in a test of `IF(0)'.
      This may not be what you expect.  In the second case, the
      comparison tests the original floating-point value to see whether
      it is non-zero.  The result of the comparison is used as an
      integer.
 
      The default return type of `IF()' (which may matter when it is
      stored into a temporary table) is calculated in MySQL 3.23 as
      follows:
 
      *Expression*                       *Return
                                         Value*
      `expr2' or `expr3' returns a       string
      string                             
      `expr2' or `expr3' returns a       floating-point
      floating-point value               
      `expr2' or `expr3' returns an      integer
      integer                            
 
      If `expr2' and `expr3' are strings, the result is case sensitive
      if either string is case sensitive (starting from MySQL 3.23.51).
 
 `IFNULL(EXPR1,EXPR2)'
      If EXPR1 is not `NULL', `IFNULL()' returns EXPR1, else it returns
      EXPR2.  `IFNULL()' returns a numeric or string value, depending on
      the context in which it is used.
 
           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'
 
      In MySQL 4.0.6 and above, the default result value of
      `IFNULL(EXPR1,EXPR2)' is the more "general" of the two expressions,
      in the order `STRING', `REAL', or `INTEGER'. The difference from
      earlier MySQL versions is mostly notable when you create a table
      based on expressions or MySQL has to internally store a value from
      `IFNULL()' in a temporary table.
 
           CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
 
      As of MySQL 4.0.6, the type for the `test' column is `CHAR(4)',
      whereas in earlier versions the type would be `BIGINT'.
 
 `NULLIF(EXPR1,EXPR2)'
      Returns `NULL' if `EXPR1 = EXPR2' is true, else returns EXPR1.
      This is the same as `CASE WHEN EXPR1 = EXPR2 THEN NULL ELSE EXPR1
      END'.
 
           mysql> SELECT NULLIF(1,1);
                   -> NULL
           mysql> SELECT NULLIF(1,2);
                   -> 1
 
      Note that MySQL evaluates `expr1' twice if the arguments are not
      equal.
 
      `NULLIF()' was added in MySQL 3.23.15.
 
 

Info Catalog

(mysql.info.gz) Non-typed Operators

(mysql.info.gz) Functions

(mysql.info.gz) String functions