Using control flow operators in MySQL

There are several control flow operators available in MySQL. This post will show how to use the CASE operator. One version of this operator employs the following syntax:

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result...] [ELSE result] END.

In this version, the CASE statement returns the result value when value=compare_value. Here is an example:

mysql> SELECT CASE 1 WHEN 1 THEN 'apple'
-> WHEN 2 THEN 'orange'
-> ELSE 'more' END;
+--------------------------------+
CASE 1 WHEN 1 THEN 'apple' WHEN 2
THEN 'orange' ELSE 'more' END
+--------------------------------+
| apple |
+--------------------------------+
1 row in set (0.00 sec)

Another version of the CASE statement is:

CASE WHEN [condition] THEN result [WHEN condition THEN result...] [ELSE result] END.

Here is how this version would work as follows:

mysql> SELECT CASE WHEN 2>1 THEN 'true'
-> ELSE 'false' END;
+-------------------------------------------+
| CASE WHEN 2>1 then 'true' ELSE 'false' END
+-------------------------------------------+
| true
+-------------------------------------------+
1 row in set (0.00 sec)

In this example, the result value is returned for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

The default return type of a CASE expression is the compatible type of all return values. If used in a string context, the result is returned as a string. If used in a numeric context, then the result is returned as a decimal or integer value.

The syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement which does not allow an ELSE NULL clause, and it is terminated with END CASE instead of END.