Table of Contents
Flow Control Functions
IFNULL()
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.
SELECT IFNULL(1,0); => 1 SELECT IFNULL(NULL,10); => 10 SELECT IFNULL(1/0,10); => 10 SELECT IFNULL(1/0,'yes'); => 'yes'
IF()
IF(expr1,expr2,expr3)
if (TRUE, expr1, expr2) ⇒ expr1
if (FALSE, expr1, expr2) ⇒ expr2
SELECT IF(1>2,2,3); => 3 SELECT IF(1<2,'yes','no'); => 'yes' SELECT IF('test'='test1','yes','no'); => 'no'
CASE Operator
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 version returns the result where value=compare-value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.
SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; => 'one' SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; => 'true'
The type of the return value (INTEGER, DOUBLE, or STRING) is the same as the type of the first returned value (the expression after the first THEN).
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2, else it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.