How does MySQL CASE work?

Question

I know the SQL 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, perhaps because I think of it as an if .

If I have a field in the user_role table, for example, that contains names such as "Manager", "Partial time", etc., how can I create a role_order field 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 to teach a person how to fish, and not give a person a fish answer.

Answer 1

CASE more like a switch statement. It has two syntaxes that you can use. The first allows you to use any required comparison expressions:

 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 when you only study one meaning and a little more concise:

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

Answer 2

CASE in MySQL is an operator and expression, where each use is slightly different.

As a statement, CASE works just like a switch statement, and is useful in stored procedures, as shown in this example in the documentation (see 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 articles:

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

In addition, both as an operator and as an expression, the first argument can be omitted, and each WHEN must accept the condition.

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

I gave this answer because the other answer does not mention that CASE can function both expression and expression. The main difference between the two is that the form of the statement ends in END CASE , and the form of the expression ends in END only.

Answer 3

I need a simple example of using a case with which I could play, for this I do not even need a table. This returns odd or even depending on whether the seconds are odd or even

 SELECT CASE MOD(SECOND(NOW()),2) WHEN 0 THEN 'odd' WHEN 1 THEN 'even' END;