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 compares expression to value 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 the ELSE 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 for condition in the WHEN clauses until it finds the first condition that is TRUE. If a match is found, result of the corresponding THEN clause is returned. Otherwise, result of the ELSE 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 is true, 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 is true, 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 to expr2, null is returned. Otherwise, expr1 is returned.
  • Example: 
    SELECT NULLIF (2,1);
+-------+
| _col0 |
+-------+
|     2 |
SELECT NULLIF (2,2);
+-------+
| _col0 |
+-------+
| NULL  |