MySQL CASE Statement and CASE Operator – Difference and Examples

There are two things called CASE in MySQL (and, by extension, the compatible MariaDB).

• The CASE Operator, for use in queries.
• The CASE Statement, for use in stored programs and procedures.

Both have similar syntax and the same purpose.

Both MySQL CASE Operator and Statement provide control over the results based on a set of conditions.

Examples in this article will use the following test data:

table_people

CASE Operator

The CASE operator is used in MySQL queries – it is the most commonly used of the CASE operator/statement (and is probably the one you’ve just googled for).

The syntax is as follows:

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

Or:

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

Note that:

• There are two variations in the syntax depending on what you are trying to achieve.
• You can provide as many WHEN statements as required.
• A final ELSE statement can be provided as a catch-all for any records which do not meet one of the CASE condition.

CASE Operator Examples

SELECT *,
CASE name
    WHEN "Bob" THEN "Robert"
    WHEN "Ted" THEN "Theodore"
    WHEN "Jim" THEN "James"
    WHEN "Tim" THEN "Timothy"
    ELSE "It's not short for anything"
END
AS fullname
FROM table_people;

If you are building case statements using more than a single column, you can omit the column name from the CASE statement and use the column names in each WHEN statement:

SELECT *,
CASE
    WHEN name = "Bob" THEN "Robert"
    WHEN name = "Ted" THEN "Theodore"
    WHEN name = "Jim" THEN "James"
    WHEN name = "Tim" THEN "Timothy"
    ELSE "It's not short for anything"
END
AS fullname
FROM table_people;

Note that in both of the above examples, the AS statement is used to name the column generated by the CASE operator. Otherwise, the column name would default to the query text, which looks very messy and is functionally useless.

Both of the above queries will return:

| id | name |                    fullname |
|----|------|-----------------------------|
|  1 |  Bob |                      Robert |
|  2 |  Ted |                    Theodore |
|  3 |  Jim |                       James |
|  4 |  Pip | It's not short for anything |
|  5 |  Zak | It's not short for anything |

CASE Statement

The CASE statement is best used for stored programs and has a slightly different syntax. Instead of providing a result for each WHEN clause to set a column value, you can provide a list of statements to be executed.

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

Or:

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

CASE Statement Example

The following example would replace the name column rather than add a new column for the full name if used in a stored procedure:

CASE name
    WHEN  'Bob' THEN
        SET name = 'Robert';
    WHEN 'Ted' THEN
        SET name = 'Theodore';
    ELSE
        SET name = 'Not Bob or Ted';
END CASE;

As with the case operator, case_value can be omitted when referring to the columns by name in each WHEN statement.

CASE 
    WHEN  name = 'Bob' THEN
        SET name = 'Robert';
    WHEN name = 'Ted' THEN
        SET name = 'Theodore';
    ELSE
        SET name = 'Not Bob or Ted';
END CASE;