How To Use CASE Statements in MySQL?

MySQL is a popular database management system that allows users to store and retrieve data. Among its many features, the CASE statement provides a useful tool for performing conditional operations on data. In this article, you’ll get to know about how to use the CASE statement in MySQL to perform conditional operations.

Understanding the CASE Statement

The CASE statement is a conditional statement that allows users to perform different actions based on different conditions. It is similar to the if-else statement found in many other programming languages. 

There are two forms of the CASE statement: the simple CASE statement and the searched CASE statement. The simple CASE statement compares an expression with a set of values and returns a result based on the matching value. The searched CASE statement compares a set of Boolean expressions and returns a result based on the first true condition.

Source

Syntax of the Simple CASE Statement

The syntax for the simple CASE statement is:

CASE expression

   WHEN value1 THEN result1

   WHEN value2 THEN result2

   WHEN value3 THEN result3

   …

   ELSE default_result

END

Here, the expression is the value being compared to the set of values. The value1, value2, value3, etc. are the values being compared to the expression. The result1, result2, result3, etc. are the results that will be returned when the corresponding value matches the expression. The default_result is the result that will be returned when none of the values match the expression.

Syntax of the Searched CASE Statement

The syntax for the searched CASE statement is:

CASE

   WHEN condition1 THEN result1

   WHEN condition2 THEN result2

   WHEN condition3 THEN result3

   …

   ELSE default_result

END

Here, the condition1, condition2, condition3, etc. are Boolean expressions being compared. The result1, result2, result3, etc. are the results that will be returned when the corresponding condition is true. The default_result is the result that will be returned when none of the conditions are true.

Examples

To better understand how to use the CASE statement in MySQL, let’s look at an example.

Example: Using the Simple CASE Statement

Let’s say you have a table called “employees” that contains information about the employees in your company. You want to create a new column called “salary_range” that categorizes the employees based on their salary. You can use the simple CASE statement to achieve this:

ALTER TABLE employees ADD COLUMN salary_range VARCHAR(20);

UPDATE employees SET salary_range =

   CASE

   WHEN salary < 50000 THEN ‘Low’

   WHEN salary BETWEEN 50000 AND 100000 THEN ‘Medium’

   ELSE ‘High’

   END;

In this example, we are adding a new column called “salary_range” to the “employees” table. We are using the UPDATE statement to update the “salary_range” column based on the values in the “salary” column. If the salary is less than 50000, we set the salary_range to “Low”. If the salary is between 50000 and 100000, we set the salary_range to “Medium”. If the salary is greater than 100000, we set the salary_range to “High”.

Conclusion

The  CASE statement in MySQL provides a useful tool for performing conditional operations on data. Whether you need to categorize data, update records based on certain conditions, or perform other operations, the CASE statement can help you achieve your goals efficiently and effectively. By understanding the syntax and examples of the simple and searched CASE statements, you can use this tool to enhance your database management skills and create more flexible and powerful queries.