Syntax and examples of control flow functions - AnalyticDB

Control flow functions let you evaluate conditions directly in SQL queries. AnalyticDB for MySQL supports four functions: CASE, IF, IFNULL, and NULLIF.

Function	Description
`CASE`	Returns a result based on matching conditions. Supports two forms: simple (value matching) and searched (Boolean expression).
`IF`	Returns one value if a condition is true, another if false or NULL.
`IFNULL`	Returns the first expression if it is not NULL; otherwise returns the second.
`NULLIF`	Returns NULL if two expressions are equal; otherwise returns the first expression.

Sample data

The examples on this page use a table named conditiontest. Run the following statements to create the table and insert sample data:

CREATE TABLE conditiontest(a INT) DISTRIBUTED BY HASH(a);
INSERT INTO conditiontest VALUES (1),(2),(3);

CASE

CASE has two forms: simple and searched.

Simple CASE

CASE expression
    WHEN value THEN result
    [WHEN value THEN result ...]
    [ELSE result]
    END

Compares expression against each value in order. Returns the result of the first matching WHEN branch. If no value matches, returns the result in the ELSE clause.

Example:

SELECT a,
      CASE a
      WHEN 1 THEN 'one'
      WHEN 2 THEN 'two'
      ELSE 'three'
      END as caseresult
FROM conditiontest;

Output:

+---+------------+
| a | caseresult |
+---+------------+
| 2 | two        |
| 1 | one        |
| 3 | three      |
+---+------------+

Searched CASE

CASE
    WHEN condition THEN result
    [WHEN condition THEN result...]
    [ELSE result]
    END

Evaluates each condition in order. Returns the result of the first condition that evaluates to true. If all conditions are false, returns the result in the ELSE clause.

Example:

SELECT a,
      CASE
      WHEN a=1 THEN 'one1'
      WHEN a=2 THEN 'two2'
      ELSE 'three3'
      END as caseresult
FROM conditiontest;

Output:

+---+------------+
| a | caseresult |
+---+------------+
| 1 | one1       |
| 3 | three3     |
| 2 | two2       |
+---+------------+

IF

Two-argument form

IF(condition, true_value)

Returns true_value if condition is true. Returns NULL if condition is false or NULL.

Example — condition is true:

SELECT IF((2+3)>4,5);

Output:

+-------+
| _col0 |
+-------+
|     5 |

Three-argument form

IF(condition, true_value, false_value)

Returns true_value if condition is true. Returns false_value if condition is false or NULL.

Example — condition is false:

SELECT IF((2+3)<5,5,6);

Output:

+-------+
| _col0 |
+-------+
|     6 |

IFNULL

IFNULL(expr1, expr2)

Returns expr1 if it is not NULL. Returns expr2 if expr1 is NULL.

Example 1 — expr1 is NULL:

SELECT IFNULL(NULL,2);

Output:

+-------+
| _col0 |
+-------+
|     2 |
+-------+

Example 2 — expr1 is not NULL:

SELECT IFNULL(1,0);

Output:

+-------+
| _col0 |
+-------+
|     1 |
+-------+

NULLIF

NULLIF(expr1, expr2)

Returns NULL if expr1 equals expr2. Returns expr1 otherwise.

Example 1 — expressions are not equal:

SELECT NULLIF(2,1);

Output:

+-------+
| _col0 |
+-------+
|     2 |
+-------+

Example 2 — expressions are equal:

SELECT NULLIF(2,2);

Output:

+-------+
| _col0 |
+-------+
| NULL  |
+-------+