SQL CASE Expression

The SQL CASE expression is a control flow statement that allows you to add conditional logic to your SQL queries. It's similar to the if-else statements found in many programming languages. The CASE expression goes through conditions and returns a value when the first condition is met. If no conditions are true, it returns a default value.

Here’s the general syntax:

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

Let's look at some examples to better understand how the CASE expression works.

Example 1: Basic CASE Expression

Suppose you have a table Employees with columns EmployeeID, Name, and DepartmentID. You want to create a report that shows each employee's department name instead of the department ID.

SELECT 
    EmployeeID, 
    Name,
    DepartmentID,
    CASE 
        WHEN DepartmentID = 1 THEN 'HR'
        WHEN DepartmentID = 2 THEN 'Finance'
        WHEN DepartmentID = 3 THEN 'Engineering'
        ELSE 'Other'
    END AS DepartmentName
FROM Employees;

Output:

Example 2: CASE Expression with Aggregate Functions

Suppose you have a table Orders with columns OrderID, CustomerID, OrderDate, and Amount. You want to categorize each order as 'Small', 'Medium', or 'Large' based on the Amount.

SELECT 
    OrderID,
    CustomerID,
    Amount,
    CASE 
        WHEN Amount < 100 THEN 'Small'
        WHEN Amount BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'Large'
    END AS OrderSize
FROM Orders;

Output:

Example 3: Nested CASE Expression

Suppose you have a table Sales with columns SaleID, ProductID, Quantity, and SaleDate. You want to determine the sales performance for each product based on the quantity sold.

SELECT 
    SaleID,
    ProductID,
    Quantity,
    CASE 
        WHEN Quantity < 10 THEN 'Low'
        WHEN Quantity BETWEEN 10 AND 50 THEN
            CASE 
                WHEN Quantity < 30 THEN 'Medium'
                ELSE 'High-Medium'
            END
        ELSE 'High'
    END AS Performance
FROM Sales;

Output:

Example 4: CASE Expression with GROUP BY

Suppose you have a table Products with columns ProductID, ProductName, and Price. You want to categorize the products into different price ranges and get the count of products in each category.

SELECT 
    CASE 
        WHEN Price < 50 THEN 'Cheap'
        WHEN Price BETWEEN 50 AND 150 THEN 'Affordable'
        ELSE 'Expensive'
    END AS PriceRange,
    COUNT(*) AS ProductCount
FROM Products
GROUP BY 
    CASE 
        WHEN Price < 50 THEN 'Cheap'
        WHEN Price BETWEEN 50 AND 150 THEN 'Affordable'
        ELSE 'Expensive'
    END;

Output:

These examples demonstrate the versatility and power of the CASE expression in SQL for implementing conditional logic directly in your queries.