This topic describes the control flow functions that are supported by AnalyticDB for MySQL.
Sample data
In this example, a table named conditiontest
is used to test the control flow functions.
Execute the following statements to create a table named conditiontest and write data to the table:
CREATE TABLE conditiontest(a INT) DISTRIBUTED BY HASH(a);
INSERT INTO conditiontest VALUES (1),(2),(3);
CASE
Syntax 1
CASE expression
WHEN value THEN result
[WHEN value THEN result ...]
[ELSE result]
END
Description: This function compares the value of
expression
with the values ofvalue
in sequence. If the value ofexpression
is the same as that ofvalue
, this function returns theresult
value ofvalue
. If none of the values ofvalue
is the same as the value ofexpression
, this function returns theresult
value ofELSE
.Example:
SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'three' END as caseresult FROM conditiontest;
The following information is returned:
+---+------------+ | a | caseresult | +---+------------+ | 2 | two | | 1 | one | | 3 | three | +---+------------+
Syntax 2
CASE
WHEN condition THEN result
[WHEN condition THEN result...]
[ELSE result]
END
Description: This function checks
condition
in sequence. If acondition
is true, this function returns theresult
value of thecondition
. If allconditions
are false, this function returns theresult
value ofELSE
.Example:
SELECT a, CASE WHEN a=1 THEN 'one1' WHEN a=2 THEN 'two2' ELSE 'three3' END as caseresult FROM conditiontest;
The following information is returned:
+---+------------+ | a | caseresult | +---+------------+ | 1 | one1 | | 3 | three3 | | 2 | two2 | +---+------------+
IF
Syntax 1
IF(condition, true_value)
Description: If a
condition
istrue
, this function returns the value oftrue_value
. Otherwise, this function returnsnull
.Example:
SELECT IF((2+3)>4,5);
+-------+ | _col0 | +-------+ | 5 |
Syntax 2
IF(condition, true_value, false_value)
Description: If a
condition
istrue
, this function returns the value oftrue_value
. Otherwise, this function returns the value offalse_value
.Example:
SELECT IF((2+3)<5,5,6);
+-------+ | _col0 | +-------+ | 6 |
IFNULL
IFNULL(expr1,expr2)
Description: If
expr1
is not null, this function returns the value ofexpr1
. Otherwise, this function returns the value ofexpr2
.Examples:
Example 1:
SELECT IFNULL(NULL,2);
+-------+ | _col0 | +-------+ | 2 | +-------+
Example 2:
SELECT IFNULL(1,0);
+-------+ | _col0 | +-------+ | 1 | +-------+
NULLIF
NULLIF(expr1,expr2)
Description: If the value of
expr1
is the same as that ofexpr2
, this function returnsnull
. Otherwise, this function returns the value ofexpr1
.Examples:
Example 1:
SELECT NULLIF (2,1);
+-------+ | _col0 | +-------+ | 2 | +-------+
Example 2:
SELECT NULLIF (2,2);
+-------+ | _col0 | +-------+ | NULL | +-------+