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:

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:

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:

Query:

The result:

MySQL CASE

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:

The result:

MySQL CASE Group

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:

The result:

This is Friday

Similarly, you may use text:

The output:

Brown

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: