This topic describes the control flow functions of AnalyticDB for MySQL.
The data from the conditiontest
table is used in the examples for the control flow functions in this topic.
create table conditiontest(a int) distributed by hash(a);
insert into conditiontest values (1),(2),(3);
SELECT * FROM conditiontest;
+---+
| a |
+---+
| 2 |
| 1 |
| 3 |
CASE
CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
- Description: The simple
CASE
expression sequentially comparesexpression
tovalue
in the WHEN clauses until it finds a match. If a match is found,result
of the corresponding THEN clause is returned. Otherwise,result
of theELSE
clause is returned. - Example:
SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'three' END as caseresult FROM conditiontest; +---+------------+ | a | caseresult | +---+------------+ | 2 | two | | 1 | one | | 3 | three |
CASE
WHEN condition THEN result
[ WHEN ... ]
[ ELSE result ]
END
- Description: The advanced
CASE
expression sequentially searches forcondition
in the WHEN clauses until it finds the firstcondition
that isTRUE
. If a match is found,result
of the corresponding THEN clause is returned. Otherwise,result
of theELSE
clause is returned. - Example:
SELECT a, CASE a WHEN a=1 THEN 'one1' WHEN a=2 THEN 'two2' ELSE 'three3' END as caseresult FROM conditiontest; +---+------------+ | a | caseresult | +---+------------+ | 1 | one1 | | 3 | three3 | | 2 | three3 |
IF
if(condition, true_value)
- Description: If the
condition
istrue
,true_value
is returned. Otherwise,null
is returned. - Example:
SELECT IF((2+3)>4,5); +-------+ | _col0 | +-------+ | 5 |
if(condition, true_value, false_value)
- Description: If the
condition
istrue
,true_value
is returned. Otherwise,false_value
is returned. - Example:
SELECT IF((2+3)<5,5,6) +-------+ | _col0 | +-------+ | 6 |
IFNULL
IFNULL(expr1,expr2)
- Description: If
expr1
is not null,expr1
is returned. Otherwise,expr2
is returned. - Example:
SELECT IFNULL(NULL,2); +-------+ | _col0 | +-------+ | 2 | SELECT IFNULL(1,0); +-------+ | _col0 | +-------+ | 1 |
NULLIF
NULLIF(expr1,expr2)
- Description: If
expr1
is equal toexpr2
,null
is returned. Otherwise,expr1
is returned. - Example:
SELECT NULLIF (2,1); +-------+ | _col0 | +-------+ | 2 | SELECT NULLIF (2,2); +-------+ | _col0 | +-------+ | NULL |