Course contentsShow
← Data Science
Lesson 1036 of 2,14526. Advanced SQLFree lesson

Nested CASE Statements

Build complex multi-level conditional logic by nesting CASE expressions within each other.

Nested CASE Statements

You'll learn how to build multi-level decision trees within SQL queries by placing CASE expressions inside other CASE expressions.

Core Idea

Just as you can nest if statements in programming languages, you can nest CASE expressions within SQL. This allows you to create complex, hierarchical conditional logic where the evaluation of one condition determines which set of sub-conditions gets checked next.

Think of it like a decision tree: first you check a major category, then within each category, you apply more specific rules.

How It Works

You place a complete CASE expression where you'd normally put a simple value in another CASE statement—typically in the THEN or ELSE clauses:

SELECT 
    employee_name,
    salary,
    department,
    CASE 
        WHEN department = 'Sales' THEN
            CASE 
                WHEN salary > 80000 THEN 'Sales - Senior'
                WHEN salary > 50000 THEN 'Sales - Mid'
                ELSE 'Sales - Junior'
            END
        WHEN department = 'Engineering' THEN
            CASE 
                WHEN salary > 100000 THEN 'Engineering - Lead'
                WHEN salary > 70000 THEN 'Engineering - Senior'
                ELSE 'Engineering - Standard'
            END
        ELSE 'Other - Standard'
    END AS job_level
FROM employees;

Why Use Nested CASE?

Nested CASE statements let you apply different logic paths based on initial conditions. In the example above, Sales and Engineering departments have different salary thresholds for their levels—something you couldn't easily express with a flat CASE structure.

Best Practices

Keep nesting to 2-3 levels maximum for readability. Beyond that, consider breaking logic into CTEs or separate columns.

Key Takeaway: Nested CASE expressions enable multi-level conditional logic by placing one CASE inside another's THEN or ELSE clause, letting you build department-specific rules or hierarchical decision trees directly in SQL.