Apprendre Chaining CTEs: Multiple and Nested CTEs

Glissez pour afficher le menu

When you need to break down complex SQL queries into manageable, readable parts, using multiple Common Table Expressions (CTEs) in a single WITH clause is a powerful technique. Instead of writing one large, hard-to-follow query, you can define several CTEs, each handling a specific subtask. This modular approach makes your SQL easier to maintain, debug, and extend. With multiple CTEs, you can chain logical steps—such as filtering, aggregating, or joining data—each in its own named section. This not only clarifies your intent but also supports code reuse within the same query.


              123456789101112
            
WITH high_earners AS (
    SELECT employee_id, first_name, last_name, salary, department_id
    FROM employees
    WHERE salary > 55000
),
high_earner_departments AS (
    SELECT d.department_id, d.department_name, e.first_name, e.last_name, e.salary
    FROM departments d
    JOIN high_earners e ON d.department_id = e.department_id
)
SELECT *
FROM high_earner_departments;

When you define multiple CTEs in a single WITH clause, you can have later CTEs reference those defined earlier. This enables you to build up complex logic step by step. In addition to chaining, you can also nest CTEs—where one CTE relies on the results of another CTE, which itself may be the result of another CTE. Nesting is especially useful when you want to perform layered calculations, such as first computing an average and then filtering based on that average. This modularity helps you avoid deeply nested subqueries and makes each logical step explicit.


              12345678910111213
            
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
above_avg_employees AS (
    SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.department_id
    FROM employees e
    JOIN dept_avg da ON e.department_id = da.department_id
    WHERE e.salary > da.avg_salary
)
SELECT *
FROM above_avg_employees;

1. What is the correct way to define multiple CTEs in a single query?

2. Fill in the blanks to complete a query with two chained CTEs.

3. Why might you use nested CTEs instead of a single complex CTE?

Tout était clair ?

Merci pour vos commentaires !

Section 1. Chapitre 4

Demandez à l'IA

Posez n'importe quelle question ou essayez l'une des questions suggérées pour commencer notre discussion

Section 1. Chapitre 4