-
CASEvalueWHEN [compare_value] THENresult[WHEN [compare_value] THENresult...] [ELSEresult] ENDCASE WHEN [condition] THENresult[WHEN [condition] THENresult...] [ELSEresult] ENDThe first version returns the
resultwherevalue=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result afterELSEis returned, orNULLif there is noELSEpart.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;-> NULLThe default return type of a
CASEexpression 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.Note: The syntax of the
CASEexpression shown here differs slightly from that of the SQLCASEstatement described in Section 17.2.10.2, “CASEStatement”, for use inside stored routines. TheCASEstatement cannot have anELSE NULLclause, and it is terminated withEND CASEinstead ofEND. -
If
expr1isTRUE(expr1<> 0 andexpr1<> NULL) thenIF()returnsexpr2; otherwise it returnsexpr3.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
expr2orexpr3is explicitlyNULL, the result type of theIF()function is the type of the non-NULLexpression.expr1is 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);-> 1In the first case shown,
IF(0.1)returns0because0.1is converted to an integer value, resulting in a test ofIF(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 expr2orexpr3returns a stringstring expr2orexpr3returns a floating-point valuefloating-point expr2orexpr3returns an integerinteger If
expr2andexpr3are both strings, the result is case sensitive if either string is case sensitive.Note: There is also an
IFstatement, which differs from theIF()function described here. See Section 17.2.10.1, “IFStatement”. -
If
expr1is notNULL,IFNULL()returnsexpr1; otherwise it returnsexpr2.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 orderSTRING,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 | char(4) | | | | | +-------+---------+------+-----+---------+-------+In this example, the type of the
testcolumn isCHAR(4). -
Returns
NULLifexpr1=expr2is true, otherwise returnsexpr1. This is the same asCASE WHENexpr1=expr2THEN NULL ELSEexpr1END.mysql>
SELECT NULLIF(1,1);-> NULL mysql>SELECT NULLIF(1,2);-> 1Note that MySQL evaluates
expr1twice if the arguments are not equal.