Conditional functions

SQL Function Description

SQL Function	Description
`CASE value WHEN value1_1 [, value1_2]* THEN result1 [WHEN value2_1 [, value2_2 ]* THEN result_2] [ELSE result_z] END`	Returns resultX when the first time value is contained in (valueX_1, valueX_2, …). When no value matches, returns result_z if it’s provided and returns `NULL` otherwise.
`CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2]* [ELSE result_z] END`	Returns resultX when the first conditionX is met. When no condition is met, returns result_z if it’s provided and returns `NULL` otherwise.
`NULLIF(value1, value2)`	Returns `NULL` if value1 is equal to value2; returns value1 otherwise. For example: `NULLIF(5, 5)` returns `NULL` `NULLIF(5, 0)` returns `5`
`COALESCE(value1, value2 [, value3]*)`	Returns the first value that’s not `NULL` from value1, value2, …. For example: `COALESCE(NULL, 5)` returns 5
`IF(condition, true_value, false_value)`	Returns the `true_value` if condition is met, otherwise `false_value`. For example: `IF(5 > 3, 'FOO', 'BAR')` returns 'FOO'.
`IFNULL(input, null_replacement)`	Returns `null_replacement` if input is `NULL`; otherwise input is returned. Compared to `COALESCE` or `CASE WHEN`, this function returns a data type that’s specific in terms of nullability. The returned type is the common type of both arguments but only nullable if the `null_replacement` is nullable. The function allows to pass nullable columns into a function or table that’s declared with a `NOT NULL` constraint. For example: `IFNULL(nullable_column, 5)` returns never `NULL`, regardless of the value of `nullable_column`.
`IS_ALPHA(string)`	Returns `TRUE` if all characters in string are letter, otherwise `FALSE`.
`IS_DECIMAL(string)`	Returns `TRUE` if string can be parsed to a valid numeric, otherwise `FALSE`.
`IS_DIGIT(string)`	Returns `TRUE` if all characters in string are digit, otherwise `FALSE`.
`GREATEST(value1[, value2]*)`	Returns the greatest value of the list of arguments. Returns `NULL` if any argument is `NULL`.
`LEAST(value1[, value2]*)`	Returns the least value of the list of arguments. Returns `NULL` if any argument is `NULL`.

CASE value WHEN value1_1 [, value1_2]* THEN result1 [WHEN value2_1 [, value2_2 ]* THEN result_2] [ELSE result_z] END

Returns resultX when the first time value is contained in (valueX_1, valueX_2, …).

When no value matches, returns result_z if it’s provided and returns NULL otherwise.

CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2]* [ELSE result_z] END

Returns resultX when the first conditionX is met. When no condition is met, returns result_z if it’s provided and returns NULL otherwise.

NULLIF(value1, value2)

Returns NULL if value1 is equal to value2; returns value1 otherwise.

For example:

NULLIF(5, 5) returns NULL
NULLIF(5, 0) returns 5

COALESCE(value1, value2 [, value3]*)

Returns the first value that’s not NULL from value1, value2, ….

For example: COALESCE(NULL, 5) returns 5

IF(condition, true_value, false_value)

Returns the true_value if condition is met, otherwise false_value.

For example: IF(5 > 3, 'FOO', 'BAR') returns 'FOO'.

IFNULL(input, null_replacement)

Returns null_replacement if input is NULL; otherwise input is returned.

Compared to COALESCE or CASE WHEN, this function returns a data type that’s specific in terms of nullability.

The returned type is the common type of both arguments but only nullable if the null_replacement is nullable.

The function allows to pass nullable columns into a function or table that’s declared with a NOT NULL constraint.

For example: IFNULL(nullable_column, 5) returns never NULL, regardless of the value of nullable_column.

IS_ALPHA(string)

Returns TRUE if all characters in string are letter, otherwise FALSE.

IS_DECIMAL(string)

Returns TRUE if string can be parsed to a valid numeric, otherwise FALSE.

IS_DIGIT(string)

Returns TRUE if all characters in string are digit, otherwise FALSE.

GREATEST(value1[, value2]*)

Returns the greatest value of the list of arguments. Returns NULL if any argument is NULL.

LEAST(value1[, value2]*)

Returns the least value of the list of arguments. Returns NULL if any argument is NULL.