CASE - (MySQL Control Flow Function)

The CASE statement in MySQL is a control flow statement that allows you to perform conditional logic within a query. It is used to evaluate a set of conditions, and based on those conditions, it returns a value.

Syntax

The basic syntax of the MySQL CASE statement is as follows:

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   ELSE AlternativeResult
END

In the above syntax, you can define multiple WHEN statements, followed by an ELSE statement. The ELSE statement is optional and specifies what to return if none of the WHEN conditions evaluate to true.

Example

Let's say we have a table called employees that contains information about employees, including their salary. We can use the CASE statement to update the salary of employees based on their departments:

UPDATE employees
SET salary = CASE
   WHEN department = 'HR' THEN salary + 500
   WHEN department = 'Finance' THEN salary + 1000
   ELSE salary
END;

In the above example, we used the CASE statement to update the salary of employees based on their department. If the employee works in the HR department, we increased their salary by 500. If the employee works in the Finance department, we increased their salary by 1000. If the employee works in any other department, we left their salary unchanged.

Output

When we run the example code above, the output will be the number of affected rows in the employees table.

Explanation

In the example above, we used the CASE statement in a MySQL UPDATE query to update the salary of employees based on their department. We defined three conditions using the WHEN keyword and updated the salary by different amounts depending on which department the employee worked in. If none of the WHEN conditions were met, the ELSE clause set the salary to its original value.

Use

The CASE statement is useful when you need to perform conditional logic in a query. It can be used to update or select data based on multiple conditions and can be used in conjunction with other MySQL functions to perform complex operations.

Important Points

• You can use any number of WHEN statements with the CASE statement.
• The ELSE statement is optional but recommended to ensure that a value is always returned.
• The CASE statement can be used in both SELECT and UPDATE queries in MySQL.

Summary

In this tutorial, we discussed the MySQL CASE statement, which is a control flow statement that allows you to perform conditional logic within a query. We covered the syntax, example, output, explanation, use, and important points of the CASE statement. With this knowledge, you can now use the CASE statement in your MySQL queries to perform conditional logic and manipulate data based on different conditions.