MySql simple case expression not returning the right data

Question

I have an employee table as follows:

+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| emp_id | fname    | lname     | start_date | end_date | superior_emp_id | dept_id | title              | assigned_branch_id |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
|      1 | Michael  | Smith     | 2005-06-22 | NULL     |            NULL |       3 | President          |                  1 |
|      2 | Susan    | Barker    | 2006-09-12 | NULL     |               1 |       3 | Vice President     |                  1 |
|      3 | Robert   | Tyler     | 2005-02-09 | NULL     |               1 |       3 | Treasurer          |                  1 |
|      4 | Susan    | Hawthorne | 2006-04-24 | NULL     |               3 |       1 | Operations Manager |                  1 |
|      5 | John     | Gooding   | 2007-11-14 | NULL     |               4 |       2 | Loan Manager       |                  1 |
|      6 | Helen    | Fleming   | 2008-03-17 | NULL     |               4 |       1 | Head Teller        |                  1 |
|      7 | Chris    | Tucker    | 2008-09-15 | NULL     |               6 |       1 | Teller             |                  1 |
|      8 | Sarah    | Parker    | 2006-12-02 | NULL     |               6 |       1 | Teller             |                  1 |
|      9 | Jane     | Grossman  | 2006-05-03 | NULL     |               6 |       1 | Teller             |                  1 |
|     10 | Paula    | Roberts   | 2006-07-27 | NULL     |               4 |       1 | Head Teller        |                  2 |
|     11 | Thomas   | Ziegler   | 2004-10-23 | NULL     |              10 |       1 | Teller             |                  2 |
|     12 | Samantha | Jameson   | 2007-01-08 | NULL     |              10 |       1 | Teller             |                  2 |
|     13 | John     | Blake     | 2004-05-11 | NULL     |               4 |       1 | Head Teller        |                  3 |
|     14 | Cindy    | Mason     | 2006-08-09 | NULL     |              13 |       1 | Teller             |                  3 |
|     15 | Frank    | Portman   | 2007-04-01 | NULL     |              13 |       1 | Teller             |                  3 |
|     16 | Theresa  | Markham   | 2005-03-15 | NULL     |               4 |       1 | Head Teller        |                  4 |
|     17 | Beth     | Fowler    | 2006-06-29 | NULL     |              16 |       1 | Teller             |                  4 |
|     18 | Rick     | Tulman    | 2006-12-12 | NULL     |              16 |       1 | Teller             |                  4 |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+

I am trying to convert data for tellers as follows:

SELECT 
    e.emp_id,
    e.fname,
    e.lname,
    CASE e.title
        WHEN 'Head Teller' THEN 'Head Teller'
        WHEN 'Teller' AND YEAR(e.start_date) > 2007 THEN 'Teller Trainee'
        WHEN 'Teller'AND YEAR(e.start_date) < 2006 THEN 'Experienced Teller'
        WHEN 'Teller' THEN 'Teller'
        ELSE 'Non-Teller'
    END `title`
FROM
    employee e

Yet employees like Michael Smith are showing up as Teller Trainee instead of Non-Teller as follows:

+--------+----------+-----------+----------------+
| emp_id | fname    | lname     | title          |
+--------+----------+-----------+----------------+
|      1 | Michael  | Smith     | Teller Trainee |
|      2 | Susan    | Barker    | Teller Trainee |
|      3 | Robert   | Tyler     | Teller Trainee |
|      4 | Susan    | Hawthorne | Teller Trainee |
|      5 | John     | Gooding   | Teller Trainee |
|      6 | Helen    | Fleming   | Head Teller    |
|      7 | Chris    | Tucker    | Teller Trainee |
|      8 | Sarah    | Parker    | Teller Trainee |
|      9 | Jane     | Grossman  | Teller Trainee |
|     10 | Paula    | Roberts   | Head Teller    |
|     11 | Thomas   | Ziegler   | Teller Trainee |
|     12 | Samantha | Jameson   | Teller Trainee |
|     13 | John     | Blake     | Head Teller    |
|     14 | Cindy    | Mason     | Teller Trainee |
|     15 | Frank    | Portman   | Teller Trainee |
|     16 | Theresa  | Markham   | Head Teller    |
|     17 | Beth     | Fowler    | Teller Trainee |
|     18 | Rick     | Tulman    | Teller Trainee |
+--------+----------+-----------+----------------+

Note: When using a Searched Case Expression as follows:

SELECT 
    e.emp_id,
    e.fname,
    e.lname,
    CASE
        WHEN e.title = 'Head Teller'
            THEN 'Head Teller'
        WHEN e.title = 'Teller'
            AND YEAR(e.start_date) > 2007
            THEN 'Teller Trainee'
        WHEN e.title = 'Teller'
            AND YEAR(e.start_date) < 2006
            THEN 'Experienced Teller'
        WHEN e.title = 'Teller'
            THEN 'Teller'
        ELSE 'Non-Teller'
    END `title`
FROM
    employee e

It outputs correctly as follows:

+--------+----------+-----------+--------------------+
| emp_id | fname    | lname     | title              |
+--------+----------+-----------+--------------------+
|      1 | Michael  | Smith     | Non-Teller         |
|      2 | Susan    | Barker    | Non-Teller         |
|      3 | Robert   | Tyler     | Non-Teller         |
|      4 | Susan    | Hawthorne | Non-Teller         |
|      5 | John     | Gooding   | Non-Teller         |
|      6 | Helen    | Fleming   | Head Teller        |
|      7 | Chris    | Tucker    | Teller Trainee     |
|      8 | Sarah    | Parker    | Teller             |
|      9 | Jane     | Grossman  | Teller             |
|     10 | Paula    | Roberts   | Head Teller        |
|     11 | Thomas   | Ziegler   | Experienced Teller |
|     12 | Samantha | Jameson   | Teller             |
|     13 | John     | Blake     | Head Teller        |
|     14 | Cindy    | Mason     | Teller             |
|     15 | Frank    | Portman   | Teller             |
|     16 | Theresa  | Markham   | Head Teller        |
|     17 | Beth     | Fowler    | Teller             |
|     18 | Rick     | Tulman    | Teller             |
+--------+----------+-----------+--------------------+

I am dumbfounded as to why the first query did not work.

Answer 1

Based on MySQL doc, it should either be

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

OR

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

Not a mix of both as you are using, my guess is MySQL doesn't flag that as an error, but doesn't behave the way you think it should.

---

Re comment from Robert Rocha:

There are two forms of CASE syntax in SQL. You could write CASE like this:

CASE 
WHEN case_value = (when_expr1) THEN ...
WHEN case_value = (when_expr2) THEN ...

But that seems like redundant typing if all your WHEN clauses compare to the same left-side case_value with simple equality conditions. So for convenience, CASE also supports a shorthand:

CASE case_value
WHEN when_expr1 THEN ...
WHEN when_expr2 THEN ...

This is fine. But the reason your usage of CASE isn't working like you expect is that you're assuming you can use an AND expression, but the order of precedence doesn't work like you think.

You wrote:

CASE e.title
WHEN 'Head Teller' THEN ...
WHEN 'Teller' AND YEAR(e.start_date) > 2007 THEN ...

And you assumed it would work as if you had done:

CASE 
WHEN (e.title = 'Head Teller') THEN ...
WHEN (e.title = 'Teller') AND (YEAR(e.start_date) > 2007) THEN ...

But in fact when you put the e.title at the start, it implicitly overrides precedence as if you had done:

CASE 
WHEN e.title = ('Head Teller') THEN ...
WHEN e.title = ('Teller' AND YEAR(e.start_date) > 2007) THEN ...

It is actually a legal comparison to compare to a boolean expression like:

A = (B AND C)

The expression B AND C is boolean, so it will evaluate to either true or false (which is really the integer values 1 and 0 in MySQL).

So you'll compare A to either 1 or 0. Since in your case A is e.title, I guess it's a string, and that string's numeric value will be 0 (any string in MySQL can evaluate to a numeric value by its leading digit characters, if any, or 0 if none).

So your e.title will be treated as 0, and the expression it compares to will be 0, because 'Teller' is treated as numeric value 0 for purposes of boolean evaluation.

Since 0 = 0 is true, that condition in your CASE is bound to be true.

The bottom line: if you have complex conditions, you can't use the shorthand version of CASE syntax. Write out each condition explicitly.

Answer 2

From: http://www.mysqltutorial.org/mysql-case-statement/

The simple CASE statement only allows you match a value of an expression against a set of distinct values. In order to perform more complex matches such as ranges, you use the searched CASE statement. The searched CASE statement is equivalent to the IF statement, however, its construct is much more readable.