Summary  
This chapter explains how to implement advanced conditional logic in code using nested and multiple CASE WHEN statements, enabling hierarchical decision-making and multi-criteria evaluations within a single query.

General domain of usage  
Human resources management

When working with real-world data, you often need to handle complex logic that goes beyond simple single-condition checks. There are scenarios where you must evaluate multiple criteria at once or even build layered decision trees inside your SQL queries. In such cases, using nested or multiple `CASE WHEN` statements allows you to express advanced logic directly within your SQL, making your queries powerful and adaptable to a wide range of business rules.

SELECT
    name,
    department,
    years_at_company,
    CASE
        WHEN department = 'Engineering' THEN
            CASE
                WHEN years_at_company >= 5 THEN 'Eligible for Senior Bonus'
                ELSE 'Eligible for Standard Bonus'
            END
        WHEN department = 'Sales' THEN
            CASE
                WHEN years_at_company >= 3 THEN 'Eligible for Sales Bonus'
                ELSE 'Not Eligible'
            END
        ELSE 'Not Eligible'
    END AS bonus_eligibility
FROM employees;

Nesting works by embedding one `CASE WHEN` statement inside another, allowing you to apply different logic based on an initial condition. In the query above, the outer `CASE` checks the employee's department. If the department is `"Engineering"`, it uses an inner `CASE` to decide between a **Senior** or **Standard** bonus based on `years_at_company`. If the department is `"Sales"`, another inner `CASE` checks if the employee qualifies for a sales bonus. This structure is helpful when your decision-making depends on more than one factor and those factors are related in a hierarchy. You should use nesting when your logic naturally breaks down into main categories with sub-decisions under each category.

SELECT
    name,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'Executive Band'
        WHEN salary >= 80000 AND salary < 100000 THEN 'Senior Band'
        WHEN salary >= 60000 OR department = 'Human Resources' THEN 'Mid Band'
        ELSE 'Entry Band'
    END AS salary_band
FROM employees;

What is a key reason to use nested CASE WHEN statements?

A comprehensive course designed to help you master the CASE WHEN statement in SQL, from basic conditional logic to advanced use cases in data analysis and reporting. Through engaging theory and real-world tasks, you'll learn to write expressive, powerful queries that adapt to complex business requirements.

Learn the basics of the CASE WHEN statement, its syntax, and how to use it for simple conditional logic in SQL queries.

Delve deeper into complex CASE WHEN expressions, including nested conditions and multiple criteria.

Apply CASE WHEN in WHERE and ORDER BY clauses to filter and sort data conditionally.

Combine CASE WHEN with aggregate functions for advanced reporting and analytics.

Nested and Multiple CASE WHEN Conditions