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.

score 111 · Accepted Answer · edited Nov 27 '15 at 05:29

111

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

edited Nov 27 '15 at 05:29

Kailas

3,173
5
42
52

answered Aug 02 '10 at 16:31

D'Arcy Rittich

167,292
40
290
283

1

What you are saying makes sense, I am still confused about where it goes inside the rest of the query I tried `SELECT *, your-code AS role_order FROM table` But I keep getting errors. Can you show an example using a whole query? Thank you! – JD Isaacks Aug 02 '10 at 16:41
7

I figured it out, You are supposed to just end with "END" not "END CASE" http://dev.mysql.com/doc/refman/5.0/en/case-statement.html Thank you! – JD Isaacks Aug 02 '10 at 16:48
For those who did not read the linked docs above, you may not read this comment either, but in case you do: Note that there are two different forms of `CASE`, with very similar syntax, but somewhat different purpose/use... See also [this question](http://stackoverflow.com/q/12436859/2359271). – Air May 02 '14 at 00:40
1

@AirThomas: Actually, what he is describing is the `CASE` *expression*, according to MySQL documentation. Whereas the OP referenced the `CASE` *statement*. – Svip Jan 02 '15 at 16:17
1

@Svip Absolutely correct, I had them reversed. Good catch. (For the sake of keeping the comments clean, +1 this when you've read it and I will delete it as well.) – Air Jan 02 '15 at 16:25

score 13 · Answer 2 · edited Jan 02 '15 at 16:57

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.

score 1 · Answer 3 · answered Nov 17 '16 at 15:33

1

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;

answered Nov 17 '16 at 15:33

zzapper

4,743
5
48
45

How does MySQL CASE work?

3 Answers3

Linked

Related