What is MySQL CASE

If you have a little idea about how if..else..elseif statement works in different programming languages then understanding the MySQL CASE function should be straight-forward.

The CASE..WHEN…THEN..END combination is like the if..else..elseif where conditions are given and the one that turns out TRUE executes its block of code.

As soon as a condition is TRUE, further conditions are not tested. If none of the condition or “case” is True then the ELSE part value is returned.

The syntax and examples below should make things clearer.

Syntax of using MySQL CASE function

The CASE function can be used in two ways in MySQL.

The first way:

CASE value

    WHEN value_compare THEN statement_list

    [WHEN value_compare THEN statement_list] ...

    [ELSE statement_list]


In this syntax, the case value is compared to the value_compare in WHEN until one of the value is equal.

As soon as a case value is found equal to the WHEN value, the statement in the corresponding THEN clause is executed.

If none of the case value is equal to the WHEN CLAUSE value then ELSE clause statement(s) execute.

It is like the SELECT..CASE statement in different programming languages.

The second way:


WHEN condition_1 THEN result_1

WHEN condition_2 THEN result_2

WHEN condition_N THEN result_N


ELSE result


In this syntax, the WHEN clause condition is tested. As soon as a condition evaluates as True, the statement(s) in that WHEN clause is executed. Again, if none of the condition is True in all WHEN clauses then the ELSE clause statement is returned.

An example of using CASE function

In this example, the search condition is used in the CASE function. For that, we have an employee table that stores staff record including their salary (for demo only).

In the first condition, the salary is checked for 4000 or more. The second condition checks salary for 5000 or more and returns the corresponding result as shown in the graphic below:


SELECT ID, employee_name,


WHEN employee_salary >=4000

THEN  "Salary is greater or equal to 4000"

WHEN employee_salary >=5000

THEN  "Salary is greater or equal to 5000"

ELSE  "Salary is less than 4000"

END AS  "What Salary?"

FROM tst_employees;

The result:


In the above graphic, the first table shows complete data while the other returned after using the CASE function.

Using CASE function with GROUP BY clause

In this query, I am using another table that stores salaries paid to each employee every month. The GROUP BY clause with COUNT function is used to get the number of salaries paid to each employee.

By using the MySQL CASE…WHEN..THEN…END, another column returned with the name “level”.

If salaries paid are three then level is senior, if two then intermediate and if one then junior level.

The query:

SELECT employee_name AS  "Employee Name", COUNT( employee_salary_paid ) AS  "No. Of Salaries",

CASE COUNT( employee_salary_paid )

WHEN 1 THEN "Junior"

WHEN 2 THEN "Intermediate"

WHEN 3 THEN "Senior"

END AS  Level

FROM tst_emp_paid_sal

GROUP BY employee_name

The result:


In the graphic above, you can see the fourth column “Level” with respective value in relation to the CASE statement where I used COUNT( employee_salary_paid ) to provide the value.

For each group result, the CASE statement evaluated the returned value from the COUNT function and the value that equated the WHEN clause is returned.

A more simple example with a table

Well, if things are still not clear then the examples below should make it easy. Now, I am using the CASE function without using any table data for looking it simple.

A static value is assigned in the CASE function and then it is evaluated in the WHEN clauses. Have a look:


WHEN 1 THEN 'This is Monday'        

WHEN 2 THEN 'This is Tuesday'        

WHEN 3 THEN 'This is Wednesday'        

WHEN 4 THEN 'This is Thursday'        

WHEN 5 THEN 'This is Friday'        

WHEN 6 THEN 'This is Saturday'        

ELSE 'This is Weekend, Hurray!'

END AS 'What is Day?';

The result:

This is Friday

Similarly, you may use text:


WHEN 'O' THEN 'Orange'        

WHEN 'R' THEN 'Red'        

WHEN 'Blk' THEN 'Black'        

WHEN 'Bwn' THEN 'Brown'                 


ELSE 'White'

END AS 'What is Color?';

The output:


In the real world queries, the source of value most probably is from the table data as shown in the first two queries. This can be simple table column value, or based on aggregated function etc.

An example of using CASE with an UPDATE statement

The following query shows using the CASE function with the UPDATE statement. We will update the employee salary column by using the CASE..WHEN.

The query:

UPDATE `tst_employees` SET `employee_salary` = CASE

    WHEN employee_salary = 2600 THEN 3000

    WHEN employee_salary = 3500 THEN 4000

    WHEN employee_salary = 4500 THEN 5000


WHERE id  in (1,2,3,4,5,6,7)