(mysql.info) control-flow-functions

Info Catalog

(mysql.info) non-typed-operators

(mysql.info) functions

(mysql.info) 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 default return type of a `CASE' expression is the compatible
      aggregated type of all return values, but also depends on the
      context in which it is used.  If used in a string context, the
      result is returned as a string. If used in a numeric context, then
      the result is returned as a decimal, real, or integer value.
 
      slightly from that of the SQL `CASE' _statement_ described in
       case-statement, for use inside stored routines. The `CASE'
      statement cannot have an `ELSE NULL' clause, and it is terminated
      with `END CASE' instead of `END'.
 
    * `IF(EXPR1,EXPR2,EXPR3)'
 
      If EXPR1 is `TRUE' (`EXPR1 <> 0' and `EXPR1 <> NULL') then `IF()'
      returns EXPR2; otherwise 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 the non-`NULL'
      expression.
 
      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 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 both strings, the result is case sensitive
      if either string is case sensitive.
 
      * There is also an `IF' _statement_, which differs from the
      `IF()' _function_ described here. See  if-statement.
 
    * `IFNULL(EXPR1,EXPR2)'
 
      If EXPR1 is not `NULL', `IFNULL()' returns EXPR1; otherwise 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'
 
      The default result value 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  | char(4) |      |     |         |       |
           +-------+---------+------+-----+---------+-------+
 
      In this example, the type of the `test' column is `CHAR(4)'.
 
    * `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'.
 
           mysql> SELECT NULLIF(1,1);
                   -> NULL
           mysql> SELECT NULLIF(1,2);
                   -> 1
 
      Note that MySQL evaluates EXPR1 twice if the arguments are not
      equal.
 

Info Catalog

(mysql.info) non-typed-operators

(mysql.info) functions

(mysql.info) string-functions