SQL CASE WHEN with multiple AND and OR

Mastering SQL CASE WHEN with Multiple AND and OR Conditions

Unlock the power of conditional logic in SQL queries using the CASE WHEN statement, combining AND and OR operators for complex data manipulation and reporting.

The CASE WHEN statement in SQL is a powerful tool for implementing conditional logic directly within your queries. It allows you to define different outcomes based on various conditions, much like if/else if/else constructs in programming languages. When combined with logical operators like AND and OR, CASE WHEN becomes incredibly versatile for handling complex business rules, categorizing data, and generating dynamic reports. This article will guide you through using CASE WHEN with multiple AND and OR conditions, providing clear examples and best practices.

Understanding CASE WHEN Syntax

Before diving into complex conditions, let's review the basic structure of a CASE WHEN statement. There are two main forms: simple CASE and searched CASE.

Simple CASE Statement

This form compares an expression to a set of simple values.

Searched CASE Statement

This form evaluates a series of boolean conditions. This is the more flexible form and the one we'll primarily use when combining AND and OR.

-- Simple CASE example
SELECT
    ProductName,
    Price,
    CASE Price
        WHEN 10 THEN 'Cheap'
        WHEN 50 THEN 'Moderate'
        ELSE 'Expensive'
    END AS PriceCategory
FROM Products;

-- Searched CASE example
SELECT
    OrderDate,
    TotalAmount,
    CASE
        WHEN TotalAmount < 100 THEN 'Small Order'
        WHEN TotalAmount >= 100 AND TotalAmount < 500 THEN 'Medium Order'
        ELSE 'Large Order'
    END AS OrderSize
FROM Orders;

Basic examples of Simple and Searched CASE WHEN statements.

Combining AND and OR within CASE WHEN

The true power of CASE WHEN emerges when you integrate AND and OR operators to create intricate conditional logic. Each WHEN clause can contain a complex boolean expression. Remember that AND typically takes precedence over OR, but it's always good practice to use parentheses () to explicitly define the order of evaluation and improve readability.

Consider a scenario where you need to categorize customers based on their age, gender, and purchase history. This requires combining multiple conditions within a single WHEN clause.

flowchart TD
    A[Start Evaluation] --> B{Customer Age < 18?}
    B -- Yes --> C[Category: Minor]
    B -- No --> D{Age >= 18 AND Age <= 65?}
    D -- Yes --> E{Gender = 'Female' AND TotalPurchases > 500?}
    E -- Yes --> F[Category: Valued Female Adult]
    E -- No --> G{Gender = 'Male' AND TotalPurchases > 300?}
    G -- Yes --> H[Category: Valued Male Adult]
    G -- No --> I[Category: Standard Adult]
    D -- No --> J[Category: Senior]
    C,F,H,I,J --> K[End Evaluation]

Decision flow for categorizing customers using combined AND/OR logic within CASE WHEN.

SELECT
    CustomerID,
    Age,
    Gender,
    TotalPurchases,
    CASE
        WHEN Age < 18 THEN 'Minor'
        WHEN Age >= 18 AND Age <= 65 AND Gender = 'Female' AND TotalPurchases > 500 THEN 'Valued Female Adult'
        WHEN Age >= 18 AND Age <= 65 AND Gender = 'Male' AND TotalPurchases > 300 THEN 'Valued Male Adult'
        WHEN Age >= 18 AND Age <= 65 THEN 'Standard Adult'
        WHEN Age > 65 THEN 'Senior'
        ELSE 'Unknown Category'
    END AS CustomerCategory
FROM Customers;

SQL query demonstrating CASE WHEN with multiple AND conditions for customer categorization.

Using OR for Alternative Conditions

The OR operator allows you to specify that if any of the conditions within a WHEN clause are true, that clause will be matched. This is useful for grouping items that meet one of several criteria.

For instance, you might want to flag products that are either out of stock or have been discontinued.

SELECT
    ProductID,
    ProductName,
    StockQuantity,
    Status,
    CASE
        WHEN StockQuantity = 0 OR Status = 'Discontinued' THEN 'Attention Required'
        WHEN StockQuantity < 10 AND Status = 'Active' THEN 'Low Stock Alert'
        ELSE 'Good Stock'
    END AS InventoryStatus
FROM Products;

Example of using OR within CASE WHEN to identify products needing attention.

Best Practices for Complex CASE WHEN Statements

When constructing CASE WHEN statements with multiple AND and OR conditions, keep the following best practices in mind:

1. Use Parentheses for Clarity: Even when operator precedence dictates the order, parentheses () make your conditions easier to read and understand, reducing the chance of logical errors.
2. Order Matters: As mentioned, the order of WHEN clauses is critical. Place more specific conditions before more general ones.
3. Include an ELSE Clause: Always include an ELSE clause to handle any cases that don't match your WHEN conditions. This prevents unexpected NULL values and makes your logic explicit.
4. Keep it Readable: If a CASE WHEN statement becomes too long or complex, consider breaking it down into smaller, more manageable parts, or even creating a user-defined function or view for better modularity.
5. Test Thoroughly: Always test your CASE WHEN logic with a diverse set of data to ensure it behaves as expected for all possible scenarios.

SELECT
    EmployeeID,
    Department,
    YearsOfService,
    PerformanceRating,
    CASE
        WHEN (Department = 'Sales' AND YearsOfService > 5 AND PerformanceRating >= 4) OR
             (Department = 'Marketing' AND YearsOfService > 3 AND PerformanceRating >= 5) THEN 'High Performer Bonus'
        WHEN (Department = 'HR' OR Department = 'Finance') AND YearsOfService > 10 THEN 'Long Service Award'
        ELSE 'Standard Employee'
    END AS RecognitionCategory
FROM Employees;

Complex CASE WHEN example using parentheses for clarity with combined AND/OR logic.