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