GNU Info

(mysql.info)Control flow functions

---

Prev: Logical Operators Up: Non-typed Operators

---

Enter node , (file) or (file)node

Control Flow Functions
......................

`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'

`NULLIF(expr1,expr2)'
     If `expr1 = expr2' is true, return `NULL' else return `expr1'.
     This is the same as `CASE WHEN x = y THEN NULL ELSE x END':
          mysql> select NULLIF(1,1);
                  -> NULL
          mysql> select NULLIF(1,2);
                  -> 1

     Note that `expr1' is evaluated twice in MySQL if the arguments are
     equal.

`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'

     `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 above, `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 Version 3.23
     as follows:

     *Expression*                          *Return value*
     expr2 or expr3 returns 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, then the result is case sensitive
     if both strings are case sensitive. (Starting from 3.23.51)

`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, which is true. If there was no matching result
     value, then the result after `ELSE' is returned. If there is no
     `ELSE' part then `NULL' is returned:

          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').