Home / Funtions and Operators / Question No: 229

Control flow functions enable you to choose between different values based on the result of an expression. The CASE construct is not a function, but it too provides flow control. It has two forms of syntax. The first looks like this:

CASE case_expr
  WHEN when_expr THEN result
  [WHEN when_expr THEN result] ...
  [ELSE result]
END

The expression case_expr is evaluated and used to determine which of the following clauses in the rest of the CASE to execute. The when_expr in the initial WHEN clause is evaluated and compared to case_expr. If the two are equal, the expression following THEN is the result of the CASE. If when_expr is not equal to case_expr, and there are any following WHEN clauses, they are handled similarly in turn. If no WHEN clause has a when_expr equal to case_expr, and there is an ELSE clause, the expression in the ELSE clause becomes the CASE result. If there is no ELSE clause the result is NULL. In the following example, CASE expression returns a string that indicates full name of language against each language_id available in the table:

mysql> SELECT CASE languageId
    ->          WHEN 1 THEN 'English'
    ->          WHEN 2 THEN 'French'
    ->          WHEN 3 THEN 'Arabic'
    ->          WHEN 4 THEN 'dutch'
    ->        ELSE 'Urdu'
    ->        END AS Language
    -> FROM language;
+----------+
| Language |
+----------+
| English  |
| French   |
| Arabic   |
| dutch    |
+----------+
4 rows in set (0.02 sec)

The second CASE syntax looks like this:

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

For this syntax, the conditional expression in each WHEN clause is executed until one is found to be true, and then its corresponding THEN expression becomes the result of the CASE. If none of them are true and there is an ELSE clause, its expression becomes the CASE result. If there is no ELSE clause the result is NULL.  The following CASE expression tests whether the value of the @val user variable is NULL or less than, greater than, or equal to 0:

mysql> SET @val = NULL;
mysql> SELECT CASE
    ->   WHEN @val IS NULL THEN '@val is NULL'
    ->   WHEN @val < 0 THEN '@val is less than 0'
    ->   WHEN @val > 0 THEN '@val is greater than 0'
    ->   ELSE '@val is 0'
    -> END AS result;
+--------------+
| result       |
+--------------+
| @val is NULL |
+--------------+