MySQL CASE Statement

In MySQL, the CASE statement can be used in stored programs to perform a complex conditional construct. It compares a list of conditions and returns a different result depending on which condition (if any) is matched.

The CASE statement is different to the CASE operator, in that the CASE statement is specifically for use in stored programs. Also, there’s a slight difference in the syntax.

Syntax

The CASE statement can be used in two ways.

Like this:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or like this:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

Example

Here’s an example of a basic stored procedure with the first CASE syntax:

DELIMITER |
CREATE PROCEDURE sp_lunch(
    IN cuisine VARCHAR(10),
    OUT dish VARCHAR(50)
)
BEGIN
  CASE cuisine
    WHEN 'Thai' THEN SET dish = 'Pad Thai';
    WHEN 'Italian' THEN SET dish = 'Pizza';
    ELSE SET dish = 'Roast of the Day';
  END CASE;
END;
|

This syntax is referred to as the simple CASE expression. The simple CASE expression compares an expression to a set of simple expressions to determine the result.

Now when we call that procedure, the output depends on our input:

CALL sp_lunch( 'Thai', @theDish );
SELECT @theDish;

Result:

+----------+
| @theDish |
+----------+
| Pad Thai |
+----------+

And with different input:

CALL sp_lunch( 'Italian', @theDish );
SELECT @theDish;

Result:

+----------+
| @theDish |
+----------+
| Pizza    |
+----------+

And when we input a value that’s not covered by any of the WHEN conditions, the ELSE clause is effected:

CALL sp_lunch( 'Aussie', @theDish );
SELECT @theDish;

Result:

+------------------+
| @theDish         |
+------------------+
| Roast of the Day |
+------------------+

See the MySQL CASE operator for an example of how the second syntax (the searched CASE expression) works.

The CASE Operator

As mentioned, in MySQL there’s a difference between the CASE statement and the CASE operator. The CASE statement is specifically for use in stored programs. Also, the CASE statement cannot have an ELSE NULL clause (the CASE operator can), and it is terminated with END CASE instead of END (which is how the CASE operator is terminated).