Flow Control Functions

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(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 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.