MySQL CASE Function

 The CASE function in MySQL is a conditional statement used in SQL queries to return values based on different conditions. It works similarly to an IF-ELSE statement in programming and is often used in SELECT, WHERE, UPDATE, or ORDER BY clauses.

Syntax

There are two main forms of the CASE function:

1. Simple CASE

This form compares a value to a set of conditions.

CASE value
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

2. Searched CASE

This form evaluates multiple boolean expressions.

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

• value: The value to be compared in a simple CASE.
• condition: The boolean expression in a searched CASE.
• result: The value to return when a condition is met.
• ELSE: Optional. Specifies the value to return if no conditions are met. If omitted and no conditions are met, it returns NULL.

---

Examples

1. Simple CASE Example

Return a grade based on a numeric score:

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

2. Searched CASE Example

Categorize products based on price ranges:

SELECT product_name,
       price,
       CASE
           WHEN price > 100 THEN 'Expensive'
           WHEN price BETWEEN 50 AND 100 THEN 'Moderate'
           ELSE 'Cheap'
       END AS price_category
FROM products;

3. Using CASE in WHERE Clause

Filter records based on a condition:

SELECT * 
FROM orders
WHERE CASE
          WHEN customer_type = 'VIP' THEN order_amount > 500
          ELSE order_amount > 1000
      END;

4. Using CASE in ORDER BY Clause

Sort products by category dynamically:

SELECT product_name, category
FROM products
ORDER BY CASE
             WHEN category = 'Electronics' THEN 1
             WHEN category = 'Clothing' THEN 2
             ELSE 3
         END;

5. Using CASE in UPDATE Statement

Update rows based on conditions:

UPDATE employees
SET bonus = CASE
                WHEN performance = 'Excellent' THEN 1000
                WHEN performance = 'Good' THEN 500
                ELSE 100
            END;

Key Points:

• The CASE function must end with END.
• It is often used to simplify complex conditions.
• Ensure all THEN results have a consistent data type for best results.