Updated on 2024-04-19 GMT+08:00

Conditional Functions

Description

Table 1 Conditional functions

Conditional Function

Description

CASE value

WHEN value1_1 [, value1_2 ]* THEN result1

[ WHEN value2_1 [, value2_2 ]* THEN result2 ]*

[ ELSE resultZ ]

END

Returns resultX when the first value in (valueX_1, valueX_2, ...) is included. If there is no matching value, returns result_z if provided, otherwise returns NULL.

CASE

WHEN condition1 THEN result1

[ WHEN condition2 THEN result2 ]*

[ ELSE resultZ ]

END

Returns resultX when the first condition X is met. If no conditions are met, returns result_z if provided, otherwise returns NULL.

NULLIF(value1, value2)

Returns NULL if value1 equals value2; otherwise, returns value1.

For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.

COALESCE(value1, value2 [, value3 ]* )

Returns the first non-NULL value from value1, value2, ....

For example, COALESCE(3, 5, 3) returns 3.

IF(condition, true_value, false_value)

Returns true_value if the condition is met, otherwise returns false_value.

For example, IF(5 > 3, 5, 3) returns 5.

IFNULL(input, null_replacement)

Returns null_replacement if the input is NULL; otherwise, returns the input. This function returns a data type that is very clear about whether it is empty or not compared to COALESCE or CASE WHEN. The returned type is the common type of the two parameters, but can only be empty if null_replacement can be empty. This function allows nullable columns to be passed to functions or tables that use NOT NULL constraints.

For example, IFNULL(nullable_column, 5) will never return NULL.

IS_ALPHA(string)

Returns true if all characters in the string are letters; otherwise, returns false.

IS_DECIMAL(string)

Returns true if the string can be parsed as a valid number; otherwise, returns false.

IS_DIGIT(string)

Returns true if all characters in the string are numbers; otherwise, returns false.

GREATEST(value1[, value2]*)

Returns the maximum value of all input parameters. If the input parameters contain NULL, returns NULL.

LEAST(value1[, value2]*)

Returns the minimum value of all input parameters. If the input parameters contain NULL, returns NULL.