JD Isaacks JD Isaacks - 17 days ago 8
MySQL Question

How does MySQL CASE work?

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

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
Comments