(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
automatically generated byinfo2html