All Products
Search
Document Center

AnalyticDB for MySQL:Control flow functions

Last Updated:Jul 04, 2023

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 of value in sequence. If the value of expression is the same as that of value, this function returns the result value of value. If none of the values of value is the same as the value of expression, this function returns the result value of ELSE.

  • 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 a condition is true, this function returns the result value of the condition. If all conditions are false, this function returns the result value of ELSE.

  • 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 is true, this function returns the value of true_value. Otherwise, this function returns null.

  • Example:

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

Syntax 2

IF(condition, true_value, false_value)
  • Description: If a condition is true, this function returns the value of true_value. Otherwise, this function returns the value of false_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 of expr1. Otherwise, this function returns the value of expr2.

  • 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 of expr2, this function returns null. Otherwise, this function returns the value of expr1.

  • Examples:

    Example 1:

    SELECT NULLIF (2,1);
    +-------+
    | _col0 |
    +-------+
    |     2 |
    +-------+

    Example 2:

    SELECT NULLIF (2,2);
    +-------+
    | _col0 |
    +-------+
    | NULL  |
    +-------+