MySQL CASE Statement

The CASE statement is used to handle conditional logic in SQL queries. It goes through conditions and returns a result when the first condition is met. If no conditions are met and there is an ELSE clause, it returns the value in the ELSE clause. If no conditions are met and there is no ELSE clause, it returns NULL.

The CASE statement can be used in SELECT, UPDATE, DELETE, or ORDER BY statements.

Syntax of the CASE Statement

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

• condition1, condition2, ...: The conditions that are checked.
• result1, result2, ...: The result returned when the respective condition is true.
• ELSE result: Optional. The result returned if none of the conditions are true. If omitted, NULL is returned when no condition matches.

Example 1: Basic CASE Example

SELECT OrderID, Quantity,
       CASE 
           WHEN Quantity > 30 THEN 'High'
           WHEN Quantity BETWEEN 10 AND 30 THEN 'Medium'
           WHEN Quantity < 10 THEN 'Low'
           ELSE 'Unknown'
       END AS OrderPriority
FROM Orders;

This query categorizes orders into High, Medium, Low, or Unknown based on the Quantity in the Orders table.

Example 2: CASE with an ELSE Clause

SELECT CustomerID, 
       CASE 
           WHEN Country = 'USA' THEN 'Domestic'
           WHEN Country = 'Canada' THEN 'Domestic'
           ELSE 'International'
       END AS CustomerType
FROM Customers;

This query classifies customers as Domestic if they are from the USA or Canada, and as International for all other countries.

Example 3: CASE in ORDER BY Clause

SELECT ProductName, Price
FROM Products
ORDER BY 
    CASE 
        WHEN Price > 50 THEN 1
        WHEN Price BETWEEN 30 AND 50 THEN 2
        ELSE 3
    END;

This query sorts the products based on their price, prioritizing high prices first, followed by medium prices, and low prices last.

Key Points:

• CASE can be used to handle complex conditional logic in SQL queries, making it very useful for data categorization or classification.
• The ELSE clause is optional; if not provided, NULL is returned when no condition is met.
• The CASE statement evaluates conditions sequentially and returns the result of the first matched condition.