The CASE conditional operator can implement logic similar to IF...THEN...ELSE without calling subprograms.
Syntax of the CASE Conditional Operator
The CASE conditional operator has two syntax forms, as follows:
CASE value WHEN compare-value THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
CASE WHEN condition THEN result [WHEN [condition] THEN result ...] [ELSE result] END
The first CASE statement returns the value of result when the first value=compare-value condition evaluates to True.
The second CASE statement returns the result when the first condition is True. If no comparison is made or the condition is True, it returns the result following ELSE. If there is no ELSE part, it returns NULL.
Examples of the CASE Conditional Operator
obclient> SELECT CASE 'B' WHEN 'A' THEN 1 WHEN 'B' THEN 2 END;
+----------------------------------------------+
| CASE 'B' WHEN 'A' THEN 1 WHEN 'B' THEN 2 END |
+----------------------------------------------+
| 2 |
+----------------------------------------------+
1 row in set
obclient> SELECT CASE CONCAT('A','B') WHEN CONCAT('AB','') THEN 'A' WHEN 'B' THEN 'B' END;
+--------------------------------------------------------------------------+
| CASE CONCAT('A','B') WHEN CONCAT('AB','') THEN 'A' WHEN 'B' THEN 'B' END |
+--------------------------------------------------------------------------+
| A |
+--------------------------------------------------------------------------+
1 row in set
obclient> SELECT CASE WHEN 1>0 THEN 'TRUE' ELSE 'FALSE' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'TRUE' ELSE 'FALSE' END |
+--------------------------------------------+
| TRUE |
+--------------------------------------------+
1 row in set