Cong LiToday, we'll introduce the usage of the following control flow functions:
1. CASE
Syntax 1:
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
Function Description:
The function matches each condition sequentially. When value=compare-value, it returns the corresponding result. If no match is found, it returns the result after ELSE. If there is no ELSE clause, it returns NULL by default.
If compare-value conditions overlap, meaning the value satisfies multiple compare-value conditions, only the first matching value is returned.
Syntax 2:
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
Function Description:
The function evaluates each condition sequentially. When a condition is TRUE, it returns the corresponding result. If all conditions are FALSE, it returns the result after ELSE. If there is no ELSE clause, it returns NULL by default.
The default return type of a CASE expression is the compatible type of all the return values, depending on its context:
- If used in a string context, it returns a string.
- If used in a numeric context, it returns a decimal value, which could be a real or integer value.
Examples
Example 1: When value=compare-value, it returns the corresponding result value.
gbase> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END FROM dual;
+-------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+-------------------------------------------------------------+
| one |
+-------------------------------------------------------------+
1 row in set
Example 2: When condition is TRUE, it returns the corresponding result value.
gbase> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END FROM dual;
+---------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+---------------------------------------------+
| true |
+---------------------------------------------+
1 row in set
Example 3: When value does not equal compare-value, it returns NULL.
gbase> SELECT CASE 'c' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END FROM dual;
+-----------------------------------------------+
| CASE 'c' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
+-----------------------------------------------+
| NULL |
+-----------------------------------------------+
1 row in set
2. DECODE
Syntax:
DECODE(value, value1, result1, value2, result2, value3, result3, ..., result)
Function Description:
Similar to CASE value WHEN value1 THEN result1 .... The only difference is that if value is NULL, it can match with a NULL value in the subsequent conditions.
Examples
Example 1: No matching value, returns the final result.
gbase> SELECT DECODE(5,1,10,2,20,3,30,4,40, 50) FROM dual;
+-----------------------------------+
| DECODE(5,1,10,2,20,3,30,4,40, 50) |
+-----------------------------------+
| 50 |
+-----------------------------------+
1 row in set
Example 2: value is an expression, matches value1, returns result1.
gbase> SELECT DECODE((2 * 5), 10, 100, 20, 200, 600) FROM dual;
+-------------------------------------+
| DECODE((2 * 5), 10, 100, 20, 200, 600) |
+-------------------------------------+
| 100 |
+-------------------------------------+
1 row in set
3. IF(expr1, expr2, expr3)
Syntax:
IF(expr1, expr2, expr3)
Function Description:
If expr1 is TRUE, IF() returns expr2. If expr1 is FALSE, 0, or NULL, it returns expr3.
The return value rules of IF() are the same as those of the CASE expression.
Examples
Example 1: expr1 is FALSE, returns expr3.
gbase> SELECT IF(FALSE, 2, 3) FROM dual;
+-------------+
| IF(FALSE, 2, 3) |
+-------------+
| 3 |
+-------------+
1 row in set
Example 2: expr1 is TRUE, returns expr2.
gbase> SELECT IF(TRUE, 'yes', 'no') FROM dual;
+--------------------+
| IF(TRUE, 'yes', 'no') |
+--------------------+
| yes |
+--------------------+
1 row in set
Example 3: expr1 is an expression, evaluates to TRUE, returns expr2.
gbase> SELECT IF(1 < 2, 'no', 'yes') FROM dual;
+---------------------------------------+
| IF(1 < 2, 'no', 'yes') |
+---------------------------------------+
| no |
+---------------------------------------+
1 row in set
Example 4: expr1 is an expression, not TRUE, returns expr3.
gbase> SELECT IF(1 > 2, NULL, 'no') FROM dual;
+-------------------+
| IF(1 > 2, NULL, 'no') |
+-------------------+
| no |
+-------------------+
1 row in set
4. IFNULL(expr1, expr2)
Syntax:
IFNULL(expr1, expr2)
Function Description:
If expr1 is not NULL, IFNULL() returns expr1; otherwise, it returns expr2.
The return value of IFNULL() is either numeric or string, depending on the context in which it is used. It is equivalent to IF(expr1, expr1, expr2).
Examples
Example 1: expr1 is not NULL, returns expr1.
gbase> SELECT IFNULL(1, 0) FROM dual;
+-------------+
| IFNULL(1, 0) |
+-------------+
| 1 |
+-------------+
1 row in set
Example 2: expr1 is NULL, returns expr2.
gbase> SELECT IFNULL(NULL, 10) FROM dual;
+-----------------+
| IFNULL(NULL, 10) |
+-----------------+
| 10 |
+-----------------+
1 row in set
5. NULLIF(expr1, expr2)
Syntax:
NULLIF(expr1, expr2)
Function Description:
If expr1 = expr2 is TRUE, returns NULL; otherwise, returns expr1.
Equivalent to CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
Examples
Example 1: expr1 = expr2, returns NULL.
gbase> SELECT NULLIF(1, 1) FROM dual;
+-------------+
| NULLIF(1, 1) |
+-------------+
| NULL |
+-------------+
1 row in set
Example 2: expr1 != expr2, returns expr1.
gbase> SELECT NULLIF(1, 2) FROM dual;
+-------------+
| NULLIF(1, 2) |
+-------------+
| 1 |
+-------------+
1 row in set
I hope today's content is helpful to you. Thanks for reading!
