Skip to main content

CASE conditional operator

The CASE conditional operator can implement logic similar to IF...THEN...ELSE without calling a subprogram.

Syntax of the CASE conditional operator​

The CASE conditional operator has two syntaxes, 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 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​

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 (0.001 sec)

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 (0.001 sec)

SELECT CASE WHEN 1>0 THEN 'TRUE' ELSE 'FALSE' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'TRUE' ELSE 'FALSE' END |
+--------------------------------------------+
| TRUE                                       |
+--------------------------------------------+
1 row in set (0.001 sec)

Contents

Edit this page