How does MySQL CASE work?

Question

I know it suppose to be like:

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

But I do not understand this, (maybe because I keep trying to think of it like an if statement?)

But for example if I have a field on a table called user_role, which are names like "Manager", "Part Time", etc. and I want to generate a field (role_order) and give it a different number depending on the role, for example, user_role = "Manager" then role_order = 5.

How can I do this?

Please not I am more looking for a teach a man to fish answer than a give a man a fish answer.

Thanks!!!

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 CASE 

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 CASE