Links
Comment on page

Conditional Functions

CASE

CASE WHEN <condition1> THEN <return1> [...] [ELSE <else>] END
Description
Find values that match <condition> and return the corresponding value. <condition> must be a boolean expression (ex: age>20)
Example
CASE WHEN age < 20 THEN "teenager"
WHEN age < 60 THEN "adult"
ELSE "elder adult"
END

IF

IF(condition, true_result, else_result)
Description
Check whether a value meets a condition and assign it to different values. If TRUE, it returns true_value. Otherwise, it returns else_value.
Example
IF(age > 20, "adult", "nonadult")
IF(close_price > open_price, "higher", "lower")

IFNULL

IFNULL(value, null_replacement)
Description
Check whether a value is NULL. If the value is not null, it returns the original value. Otherwise, it returns null_replacement.
Example
IFNULL(NULL, -1)
Result: -1
IFNULL(profit, 0)

NULLIF

NULLIF(value1, value2)
Description
Check whether value1 is equal to value2. Returns NULL if TRUE, otherwise returns the value1.
Example
NULLIF(0, 0)
Result: NULL
NULLIF(10, 0)
Result: 10