How does MySQL CASE work?

Question

I know that SQL's CASE syntax is as follows:

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

However, I don't understand how this works, possibly because I'm thinking about it as about an if statement.

If I have a field in table user_role, for example, which contains names like "Manager", "Part Time" etc., how do I generate a field role_order with a different number depending on the role. In the case of this example, "if user_role = 'Manager' then role_order = 5".

Please note I am looking for a teach a man how to fish answer rather than give a man a fish answer.

Answer 1

CASE is more like a switch statement. It has two syntaxes you can use. The first lets you use any compare statements you want:

CASE      WHEN user_role = 'Manager' then 4     WHEN user_name = 'Tom' then 27     WHEN columnA <> columnB then 99     ELSE -1 --unknown END 

The second style is for when you are only examining one value, and is a little more succinct:

CASE user_role     WHEN 'Manager' then 4     WHEN 'Part Time' then 7     ELSE -1 --unknown END 

Answer 2

CASE in MySQL is both a statement and an expression, where each usage is slightly different.

As a statement, CASE works much like a switch statement and is useful in stored procedures, as shown in this example from the documentation (linked above):

DELIMITER |  CREATE PROCEDURE p()   BEGIN     DECLARE v INT DEFAULT 1;      CASE v       WHEN 2 THEN SELECT v;       WHEN 3 THEN SELECT 0;       ELSE         BEGIN -- Do other stuff         END;     END CASE;   END;   | 

However, as an expression it can be used in clauses:

SELECT *   FROM employees   ORDER BY     CASE title       WHEN "President" THEN 1       WHEN "Manager" THEN 2       ELSE 3     END, surname 

Additionally, both as a statement and as an expression, the first argument can be omitted and each WHEN must take a condition.

SELECT *   FROM employees   ORDER BY     CASE        WHEN title = "President" THEN 1       WHEN title = "Manager" THEN 2       ELSE 3     END, surname 

I provided this answer because the other answer fails to mention that CASE can function both as a statement and as an expression. The major difference between them is that the statement form ends with END CASE and the expression form ends with just END.