How can I use a case statement with two conditions in MySQL?

You can use a CASE statement in MySQL with two or more conditions by specifying them within the `WHEN` clauses. The syntax allows you to check multiple conditions and return a different result for each.

Here's how you can do it:

1. Basic Syntax with Multiple Conditions:

The basic structure of a CASE statement with multiple conditions is as follows:

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

2. Example with Two Conditions:

Let's say you have a table named 'products' with columns 'price' and 'quantity'. You want to categorize products based on the following conditions:

- If the price is greater than 100, the product is 'Expensive'.

- If the quantity is less than 10, the product is 'Low Stock'.

- Otherwise, the product is 'Normal'.

Here's the MySQL query using a CASE statement:

SELECT product_name, price, quantity, CASE WHEN price > 100 THEN 'Expensive' WHEN quantity < 10 THEN 'Low Stock' ELSE 'Normal' END AS product_category FROM products;

3. Important Considerations:

- The conditions are evaluated in the order they are specified in the CASE statement. As soon as a condition is met, the corresponding result is returned, and no further conditions are checked.

- The ELSE clause is optional. If you do not include it and none of the conditions match, the result will be NULL.

- You can use multiple `WHEN` clauses to evaluate different conditions as needed.

4. Combining Conditions with AND/OR:

You can use AND/OR operators within the `WHEN` clauses to create more complex conditions.

Example:

CASE WHEN price > 100 AND quantity > 5 THEN 'High Demand' WHEN price < 50 OR quantity < 2 THEN 'Clearance' ELSE 'Regular' END

By using these methods, you can create powerful and flexible queries in MySQL using the `CASE` statement with multiple conditions.

Learn more about this