Control Flow Functions

Owned by Boaz Raufman
Feb 26, 2019

CASE

The CASE function implements a complex conditional construct. 
There are two variants for the CASE syntax:

Simple case:

CASE value
WHEN [compare_value] THEN result
[WHEN [compare_value] THEN result] ... 
[ELSE result] END 

Searched case:

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.

Examples:

CASE city when 'NY' then 'New York' when 'CA' then 'California' else 'other' end

CASE when city in ('NY','CA') then 'US' else 'other' end


IF
if(expr1,expr2,expr3) - If expr1 is TRUE (expr1<> 0 and expr1 <> NULL) then return expr2; otherwise it returns expr3.
This function returns a numeric or string value, depending on the context in which it is used.

Example:
if(city='NY','New York','other')


IFNULL
ifnull(expr1,expr2) - If expr1 is not NULL then returns expr1; otherwise it returns expr2.
The function returns a numeric or string value, depending on the context in which it is used.

Example:
ifnull(city,'other')


NULLIF
nullif(expr1,expr2) - Returns NULL if expr1=expr2 is true, otherwise it returns expr1.

Example:
nullif(city,'other')