CASE
value
WHENcompare_value
THENresult
[WHENcompare_value
THENresult
...] [ELSEresult
] ENDCASE WHEN
condition
THENresult
[WHENcondition
THENresult
...] [ELSEresult
] ENDThe first
CASE
syntax returns theresult
for the first
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 aftervalue
=compare_value
ELSE
is returned, orNULL
if there is noELSE
part.NoteThe syntax of the
CASE
operator described here differs slightly from that of the SQLCASE
statement described in Section 15.6.5.1, “CASE Statement”, for use inside stored programs. TheCASE
statement cannot have anELSE NULL
clause, and it is terminated withEND CASE
instead ofEND
.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 isDECIMAL
.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
, orBIGINT
).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 unsignedINT
, the result is signedBIGINT
.The exception is unsigned
BIGINT
combined with any signed integer type. The result isDECIMAL
with sufficient precision and scale 0.
If all types are
BIT
, the result isBIT
. Otherwise,BIT
arguments are treated similar toBIGINT
.If all types are
YEAR
, the result isYEAR
. Otherwise,YEAR
arguments are treated similar toINT
.If all types are character string (
CHAR
orVARCHAR
), the result isVARCHAR
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
andENUM
are treated similar toVARCHAR
; the result isVARCHAR
.If all types are temporal, the result is temporal:
If all types are
GEOMETRY
, the result isGEOMETRY
.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' -> 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
isTRUE
(
andexpr1
<> 0
),expr1
IS NOT NULLIF()
returnsexpr2
. Otherwise, it returnsexpr3
.NoteThere is also an
IF
statement, which differs from theIF()
function described here. See Section 15.6.5.2, “IF Statement”.If only one of
expr2
orexpr3
is explicitlyNULL
, the result type of theIF()
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
orexpr3
produce a string, the result is a string.If
expr2
andexpr3
are both strings, the result is case-sensitive if either string is case-sensitive.If
expr2
orexpr3
produce a floating-point value, the result is a floating-point value.If
expr2
orexpr3
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'
If
expr1
is notNULL
,IFNULL()
returnsexpr1
; otherwise it returnsexpr2
.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(
is the more “general” of the two expressions, in the orderexpr1
,expr2
)STRING
,REAL
, orINTEGER
. Consider the case of a table based on expressions or where MySQL must internally store a value returned byIFNULL()
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 isVARBINARY(4)
(a string type).Returns
NULL
if
is true, otherwise returnsexpr1
=expr2
expr1
. This is the same asCASE WHEN
.expr1
=expr2
THEN NULL ELSEexpr1
ENDThe return value has the same type as the first argument.
mysql> SELECT NULLIF(1,1); -> NULL mysql> SELECT NULLIF(1,2); -> 1
NoteMySQL evaluates
expr1
twice if the arguments are not equal.
The handling of system variable values by these functions changed in MySQL 8.0.22. For each of these functions, if the first argument contains only characters present in the character set and collation used by the second argument (and it is constant), the latter character set and collation is used to make the comparison. In MySQL 8.0.22 and later, system variable values are handled as column values of the same character and collation. Some queries using these functions with system variables that were previously successful may subsequently be rejected with Illegal mix of collations. In such cases, you should cast the system variable to the correct character set and collation.