How to Use a CASE Statement With Multiple Conditions

While working with huge volumes of data, the user wants to perform actions based on multiple conditions. In MySQL, the “CASE” statement provides an effective way to handle such situations. The “CASE” statement is used to define multiple conditions and their corresponding actions to be executed when these conditions are fulfilled. This post will discuss how to use the “CASE” statement in MySQL with multiple conditions using logical operators “AND” and “OR“.

Prerequisite: Login to the Local MySQL Server

Before beginning this post, make sure to log in to your local MySQL server using this syntax:

mysql -u <username> -p

Provide the name of your MySQL server and the username for this post is “md” so the command would become:

mysql -u md -p

After the successful login, change the database in which you want work using the syntax:

use <database-name>;

The name of the database, for this post is “linuxhint” so the command will be:

use linuxhint;

The output will display the message “Database changed”:

You have successfully changed to your desired database.

What is the “CASE” Statement in MySQL?

The “CASE” statement is a conditional expression in MySQL to define conditions and corresponding actions to be executed based on those conditions. You can define conditions to execute their corresponding actions if the condition is true; otherwise, run the “ELSE” action. Multiple conditions can be defined in one “WHEN” clause using the logical operators in between them.

Syntax of the “CASE” Statement

The syntax of the “CASE” statement is:

SELECT *,
CASE
WHEN [Condition_1] THEN [Output1]
WHEN [Condition_2] THEN [Output2]
ELSE [Output3]
END AS [column-name]
FROM [table-name];

Lets breakdown this syntax, here:

The “SELECT *” is used to select all columns of the specified table
The “CASE” statement starts with the “CASE” keyword followed by two or more “WHEN” clauses to define multiple conditions.
Each “WHEN” clause defines a condition, If the condition is true, then the corresponding output is returned that is stated after the “THEN” clause
If no condition is true the output is returned that is stated in the “ELSE” clause
The “END” keyword is utilized to mark the end of the “CASE” statement
The “AS [column-name]” is used to define a name for the column that will display the result of the “CASE” statement

Example of CASE Statement For Range-based Grouping
To define the output for grouping based on a condition if the “Id” from the table “employee” comes in the range defined in the “WHEN” clause and display its output in the column named “Id”. Run the command given below:

SELECT *,
CASE
WHEN Id BETWEEN 1 AND 5 THEN '1-5'
WHEN Id BETWEEN 6 AND 10 THEN '6-10'
ELSE 'Greater than 10'
END AS Id
FROM employee;

The output is displaying “1-5” where the “Id” lies between “1” to “5”, “6-10” where the “Id” lies in “6” to “10” otherwise if both conditions are not fulfilled the output is displaying “Greater than 10”:

CASE Statement With Multiple Conditions Using Logical “AND” Operator

The “CASE” statement can define multiple conditions using the logical “AND” operator. The syntax for the “WHEN” operator with multiple conditions using the “AND” operator is given below:

SELECT *,
CASE
WHEN [Condition_1] AND [Condition_2] THEN [Output]
WHEN [Condition_3] AND [Condition_4] THEN [Output]
ELSE [Output]
END AS [column-name]
FROM [table-name];

Let’s see an example to “SELECT” “Id”, “CompanyName” and “ContactName” columns from the table “employee” to display “Category #1” if the “City” and “Country” are equal to “Osaka” and “Japan” respectively or display “Category #2” if they are equal to “London” and “UK”. if conditions in the “WHEN” clause do not meet return “Unknown”. The output should display in a column named “City”. The command for the given example is provided below:

SELECT Id, CompanyName, ContactName,
CASE
WHEN City = 'Osaka' AND Country = 'Japan' THEN 'Category #1'
WHEN City = 'London' AND Country = 'UK' THEN 'Category #2'
ELSE 'Unknown'
END AS City
FROM employee;

The output is displaying a column “City” that is categorizing records based on multiple conditions:

Let’s see another example to “SELECT” “Id” and “ProductName” columns from the table “Product” to display “Low Price”, “Middle Price” and “High Price” if the “UnitPrice” lies in the defined ranges using the comparison operators and the “AND” operator in between two conditions in a “WHEN” clause. Otherwise, If the “WHEN” clause conditions are not fulfilled, return “Unknown”. The output should display in a column named “UnitPrice”. The command for the given example is provided below:

SELECT Id, ProductName,
CASE
WHEN UnitPrice >= 1 AND UnitPrice = 16 AND UnitPrice = 31 THEN 'High Price'
ELSE 'Unknown'
END AS UnitPrice
FROM Product;

The output is displaying in the column “UnitPrice”:

CASE Statement With Multiple Conditions Using Logical “OR” Operator

The “CASE” statement can define multiple conditions using the logical “OR” operator. The syntax for the “WHEN” operator with multiple conditions using the “OR” operator is given below:

SELECT *,
CASE
WHEN [Condition_1] OR [Condition_2] THEN [Output]
WHEN [Condition_3] OR [Condition_4] THEN [Output]
ELSE [Output]
END AS [column-name]
FROM [table-name];

Let’s see an example, to “SELECT” “Id”, “CompanyName” and “ContactName” columns from the table “employee” to display categories if the “City” and “Country” are equal to a certain value. The “OR” operator is used between two conditions in a “WHEN” clause. Otherwise, If the “WHEN” clause conditions are not fulfilled, return “Unknown”. The output should display in a column named “City”. The command for the given-example is provided below:

SELECT Id, CompanyName, ContactName,
CASE
WHEN City = 'Osaka' OR Country = 'Japan' THEN 'Category #1'
WHEN City = 'London' OR Country = 'UK' THEN 'Category #2'
WHEN Country = 'USA' THEN 'Category #3'
ELSE 'Unknown'
END AS City
FROM employee;

The output is displaying the values in the column “City” extracted through “CASE” statement with multiple conditions:

Let’s see another example, to “SELECT” “Id”, “FirstName”, “LastName” and “Phone” columns from the table “Customer” to display categories if the “City” or the “Country” value is equal to the defined values. If conditions in the “WHEN” clause do not meet, return “Unknown”. The output should display in a column named “City”. The command for the given-example is provided below:

SELECT Id, FirstName, LastName, Phone,
CASE
WHEN City = 'Berlin' OR Country = 'Germany' THEN 'Category #1'
WHEN City = 'London' OR Country = 'UK' THEN 'Category #2'
WHEN City = 'Luleå' OR Country = 'Sweden' THEN 'Category #3'
WHEN City = 'Strasbourg' OR Country = 'France' THEN 'Category #4'
WHEN City = 'Graz' OR Country = 'Austria' THEN 'Category #5'
ELSE 'Unknown'
END AS City
FROM Customer;

The query will execute and return the output containing the column “City” to display categories according to multiple conditions:

That sums up all the different methods of using the case statement with multiple conditions.

Conclusion

The “CASE” statement in MySQL is used to define complex and dynamic logic by defining multiple conditions in one “WHEN” clause using the logical “AND” and “OR” operators. The “ELSE” clause is executed if no condition is fulfilled. At the end of the statement, ensure that you use the “END” keyword.