- 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:
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
CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END
- 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 |
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
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;