12.5 Flow Control Functions

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' operator described here differs slightly from that of the SQL note 'CASE': case. statement described in note case::, for use inside stored programs. The *note 'CASE': 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 IS NOT NULL'), 'IF()' returns EXPR2. Otherwise, it returns EXPR3.

Note:

There is also an note 'IF': if. statement, which differs from the 'IF()' function described here. See note if::.

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 *note 'VARBINARY(4)': binary-varbinary. (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.

File: manual.info.tmp, Node: numeric-functions, Next: date-and-time-functions, Prev: flow-control-functions, Up: functions