MySQL CASE

The MySQL CASE statement is used to perform conditional logic within a SQL query. It allows you to conditionally choose an expression based on one or more conditions. The basic syntax of the CASE statement is as follows:

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

Here’s a breakdown of each part of the CASE statement:

• CASE: This keyword starts the CASE statement.
• WHEN condition THEN result: In this part, you specify the conditions that should be evaluated. If a condition is true, the corresponding result is returned. You can have multiple WHEN clauses to check different conditions.
• ELSE else_result: This is an optional part. If none of the conditions in the WHEN clauses are true, the else_result is returned.
• END: This keyword marks the end of the CASE statement.

Here are some examples of how to use the CASE statement in MySQL:

1. Basic CASE statement to assign a grade based on a score:

SELECT student_name, score,
       CASE
           WHEN score >= 90 THEN 'A'
           WHEN score >= 80 THEN 'B'
           WHEN score >= 70 THEN 'C'
           WHEN score >= 60 THEN 'D'
           ELSE 'F'
       END AS grade
FROM students;

2. CASE statement with multiple conditions and different result columns:

SELECT product_id, in_stock, price,
       CASE
           WHEN in_stock = 1 AND price > 100 THEN 'Available (Expensive)'
           WHEN in_stock = 1 AND price <= 100 THEN 'Available (Affordable)'
           WHEN in_stock = 0 THEN 'Out of Stock'
           ELSE 'Unknown'
       END AS status
FROM products;

3. CASE statement with arithmetic operations:

SELECT order_id, product_name, quantity, unit_price,
       CASE
           WHEN quantity > 10 THEN quantity * unit_price
           ELSE quantity * unit_price * 1.1
       END AS total_price
FROM order_details;

The CASE statement is a versatile tool for performing conditional logic in SQL queries. It’s commonly used in the SELECT statement to generate computed columns, but it can also be used in other SQL statements like UPDATE.