1 CASE ... WHEN ... THEN ... SLSE END.
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
2 IF(expr1, expr2, expr3)
If
expr1
isTRUE
(and
expr1
<> 0) then
expr1
<> NULLIF()
returnsexpr2
;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'3NULLIF(
expr1
,expr2
)Returns
NULL
ifis true, otherwise returns
expr1
=expr2
expr1
.This is the same as
CASE WHEN
.expr1
=expr2
THEN NULL ELSEexpr1
END
mysql>SELECT NULLIF(1,1);
-> NULL mysql>SELECT NULLIF(1,2);
-> 1