The CASE conditional operator can implement logic similar to IF...THEN...ELSE without calling a stored procedure.
Syntax of the CASE conditional operator
The CASE conditional operator has two syntaxes, as shown below:
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 syntax returns the result when the comparison result of the first value=compare-value is True.
The second CASE syntax returns the result when the first condition is True. If no comparison or condition is True, it returns the result after ELSE. If there is no ELSE clause, 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