MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

`CASE`

WHEN`value`

THEN`compare_value`

[WHEN`result`

THEN`compare_value`

...] [ELSE`result`

] END`result`

`CASE WHEN`

THEN`condition`

[WHEN`result`

THEN`condition`

...] [ELSE`result`

] END`result`

The first

`CASE`

syntax returns thefor the first`result`

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=`value`

`compare_value`

`ELSE`

is returned, or`NULL`

if there is no`ELSE`

part.NoteThe syntax of the

`CASE`

*operator*described here differs slightly from that of the SQL`CASE`

*statement*described in Section 13.6.5.1, “CASE Statement”, for use inside stored programs. The`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:If all types are numeric, the aggregated type is also numeric:

If at least one argument is double precision, the result is double precision.

Otherwise, if at least one argument is

`DECIMAL`

, the result is`DECIMAL`

.Otherwise, the result is an integer type (with one exception):

If all integer types are all signed or all unsigned, the result is the same sign and the precision is the highest of all specified integer types (that is,

`TINYINT`

,`SMALLINT`

,`MEDIUMINT`

,`INT`

, or`BIGINT`

).If there is a combination of signed and unsigned integer types, the result is signed and the precision may be higher. For example, if the types are signed

`INT`

and unsigned`INT`

, the result is signed`BIGINT`

.The exception is unsigned

`BIGINT`

combined with any signed integer type. The result is`DECIMAL`

with sufficient precision and scale 0.

If all types are

`BIT`

, the result is`BIT`

. Otherwise,`BIT`

arguments are treated similar to`BIGINT`

.If all types are

`YEAR`

, the result is`YEAR`

. Otherwise,`YEAR`

arguments are treated similar to`INT`

.If all types are character string (

`CHAR`

or`VARCHAR`

), the result is`VARCHAR`

with maximum length determined by the longest character length of the operands.If all types are character or binary string, the result is

`VARBINARY`

.`SET`

and`ENUM`

are treated similar to`VARCHAR`

; the result is`VARCHAR`

.If all types are temporal, the result is temporal:

If all types are

`GEOMETRY`

, the result is`GEOMETRY`

.For all other type combinations, the result is

`VARCHAR`

.Literal

`NULL`

operands are ignored for type aggregation.

mysql>

->`SELECT CASE 1 WHEN 1 THEN 'one'`

-> 'one' mysql>`WHEN 2 THEN 'two' ELSE 'more' END;`

-> 'true' mysql>`SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;`

->`SELECT CASE BINARY 'B'`

-> NULL`WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;`

If

is`expr1`

`TRUE`

(

and<> 0`expr1`

),<> NULL`expr1`

`IF()`

returns. Otherwise, it returns`expr2`

.`expr3`

NoteThere is also an

`IF`

*statement*, which differs from the`IF()`

*function*described here. See Section 13.6.5.2, “IF Statement”.If only one of

or`expr2`

is explicitly`expr3`

`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

or`expr2`

produce a string, the result is a string.`expr3`

If

and`expr2`

are both strings, the result is case-sensitive if either string is case-sensitive.`expr3`

If

or`expr2`

produce a floating-point value, the result is a floating-point value.`expr3`

If

or`expr2`

produce an integer, the result is an integer.`expr3`

mysql>

-> 3 mysql>`SELECT IF(1>2,2,3);`

-> 'yes' mysql>`SELECT IF(1<2,'yes','no');`

-> 'no'`SELECT IF(STRCMP('test','test1'),'no','yes');`

If

is not`expr1`

`NULL`

,`IFNULL()`

returns; otherwise it returns`expr1`

.`expr2`

mysql>

-> 1 mysql>`SELECT IFNULL(1,0);`

-> 10 mysql>`SELECT IFNULL(NULL,10);`

-> 10 mysql>`SELECT IFNULL(1/0,10);`

-> 'yes'`SELECT IFNULL(1/0,'yes');`

The default return type of

`IFNULL(`

is the more “general” of the two expressions, in the order,`expr1`

)`expr2`

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

mysql>`CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;`

+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | test | varbinary(4) | NO | | | | +-------+--------------+------+-----+---------+-------+`DESCRIBE tmp;`

In this example, the type of the

`test`

column is`VARBINARY(4)`

(a string type).Returns

`NULL`

if

is true, otherwise returns=`expr1`

`expr2`

. This is the same as`expr1`

`CASE WHEN`

.=`expr1`

THEN NULL ELSE`expr2`

END`expr1`

The return value has the same type as the first argument.

mysql>

-> NULL mysql>`SELECT NULLIF(1,1);`

-> 1`SELECT NULLIF(1,2);`

NoteMySQL evaluates

twice if the arguments are not equal.`expr1`