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

User · Answer

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

User · Answer

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

User · 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  lt  gt  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

[mysql] How does MySQL CASE work?

Examples related to mysql

Examples related to case