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.
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
I wanted a simple example of the use of case that I could play with, this doesn't even need a table. This returns odd or even depending whether seconds is odd or even
SELECT CASE MOD(SECOND(NOW()),2) WHEN 0 THEN 'odd' WHEN 1 THEN 'even' END;
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.
Source: Stackoverflow.com