Historical Context & Motivation
Relational databases dominated enterprise computing throughout the 1980s and 1990s, yet the SQL language itself evolved slowly. Early SQL queries that required intermediate results had only two options: deeply nested subqueries—often three or four levels deep—or temporary tables that cluttered the database namespace. Both approaches made queries brittle and difficult to reason about. The need for a structured, inline mechanism to name and reuse intermediate result sets motivated the introduction of the Common Table Expression (CTE) into the SQL standard.
The central question that multiple CTEs answer is straightforward: How can we decompose a complex query into named, sequential stages so that each stage is independently understandable and testable? Whereas a single CTE replaces one subquery, chaining multiple CTEs transforms an entire query pipeline into a series of clearly labeled transformations—much like composing functions in a program.
Core Principles & Definitions
Before diving into syntax, it is essential to internalize a handful of foundational principles that govern how multiple CTEs operate within a single SQL statement. These principles clarify scope, ordering, and optimization behavior.
Sequential Visibility
Single WITH Keyword
Statement-Level Scope
Optimizer Freedom
Composability
cat file | grep ERROR | sort | uniq -c is clearer than a single monolithic script, a chain of CTEs is clearer than a tower of nested subqueries. Each named CTE is a self-documenting checkpoint you can inspect independently.Visual Explanation — CTE Pipeline Flow
The diagram below illustrates how a query with three CTEs flows from raw tables through intermediate named result sets to a final output. Each rounded rectangle represents a CTE, and the arrows show data dependencies. Notice how the final SELECT references only the last CTE, but that CTE itself depends on the two before it.
orders and customers), flows through three CTEs—each performing a single logical step—and culminates in a final SELECT that returns only the top-ranked customers.Observe that CTE 1 (recent_orders) handles both filtering and joining—two operations that logically belong together because they define the scope of "recent customer orders." CTE 2 (order_totals) then aggregates that scoped data per customer, and CTE 3 (ranked_customers) applies a window function to rank customers by spending. Each stage transforms the result set in exactly one conceptual way, making the query's intent immediately clear to any reviewer.
How Multiple CTEs Work — Syntax & Execution
Canonical Syntax
The general pattern for multiple CTEs uses a single WITH keyword followed by comma-separated CTE definitions. Each definition consists of a name, an optional column list, and a parenthesized query. The final statement references one or more of the defined CTEs.
Execution Semantics
Although the syntax suggests a sequential, top-to-bottom execution model, modern query optimizers treat non-recursive CTEs as inline views by default. The optimizer is free to merge CTE definitions into the outer query, push predicates through CTE boundaries, and reorder joins globally. In PostgreSQL 12+, the MATERIALIZED and NOT MATERIALIZED hints give you explicit control over whether a CTE's result is computed once and stored in a temporary work table, or inlined into the outer query plan. SQL Server similarly inlines CTEs unless they appear in recursive contexts.
WITH cte_1 AS (…) WITH cte_2 AS (…). A second WITH keyword is a syntax error. All CTE definitions must follow a single WITH, separated by commas.Column Aliasing in CTEs
You may optionally specify column names in the CTE header: cte_name (col1, col2) AS (SELECT …). This is especially useful when the inner SELECT uses expressions or aggregates that lack intuitive names. If you omit the column list, the CTE inherits column names from its SELECT clause, so aliasing in the SELECT itself serves the same purpose.
Common Multi-CTE Patterns
In practice, certain patterns recur when developers compose multiple CTEs. Understanding these patterns helps you decide how to partition query logic across CTE boundaries.
| Pattern | When to Use | Example Scenario |
|---|---|---|
| Linear Pipeline | Transformations are strictly sequential; each step depends only on the immediately preceding result. | Filter raw events → aggregate per user → rank users by activity. |
| Fan-In | Multiple independent data sources or aggregations must be combined in the final output. | Compute revenue CTE and cost CTE separately, then join to calculate profit. |
| Fork | A single cleaned or filtered dataset must be aggregated in multiple, divergent ways. | Base CTE of valid transactions; one downstream CTE sums by region, another counts by product. |
| Hybrid | Complex analytics requiring both staged transformations and merging of independent data streams. | Clean raw data in pipeline CTEs, compute a parameters CTE from config, then combine for the final report. |
Worked Example — Identifying Top-Spending Customers
Suppose we have an e-commerce database with tables orders(order_id, customer_id, order_date, amount) and customers(customer_id, name, region). We want to find the top 5 customers by total spending in the last 90 days, along with their region and order count. We will build this query using three CTEs in a linear pipeline.
WITH recent_orders AS (
SELECT o.customer_id,
c.name,
c.region,
o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
)order_totals AS (
SELECT customer_id,
name,
region,
SUM(amount) AS total_spent,
COUNT(*) AS order_count
FROM recent_orders
GROUP BY customer_id, name, region
)ranked AS (
SELECT *,
RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
FROM order_totals
)SELECT name,
region,
total_spent,
order_count,
spending_rank
FROM ranked
WHERE spending_rank <= 5
ORDER BY spending_rank;WITH recent_orders AS (
SELECT o.customer_id, c.name, c.region, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
),
order_totals AS (
SELECT customer_id, name, region,
SUM(amount) AS total_spent,
COUNT(*) AS order_count
FROM recent_orders
GROUP BY customer_id, name, region
),
ranked AS (
SELECT *, RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
FROM order_totals
)
SELECT name, region, total_spent, order_count, spending_rank
FROM ranked
WHERE spending_rank <= 5
ORDER BY spending_rank;Multiple CTEs vs. Alternatives
Multiple CTEs are not the only way to decompose complex queries. Nested subqueries, temporary tables, views, and even application-level code can achieve similar results. The table below compares multiple CTEs against these alternatives on several dimensions that matter in practice.
| Criterion | Multiple CTEs | Nested Subqueries | Temp Tables |
|---|---|---|---|
| Readability | Excellent — named stages read top-to-bottom. | Poor — deeply nested, read inside-out. | Good — named, but scattered across statements. |
| Scope | Single statement only. | Single statement only. | Session-level; persists until dropped or session ends. |
| Reusability within query | Can be referenced multiple times in the same statement. | Must duplicate subquery text if referenced more than once. | Can be referenced in multiple statements. |
| Optimization | Optimizer may inline; full predicate pushdown possible. | Optimizer sees the full query tree directly. | Forces materialization; optimizer sees each statement independently. |
| Debugging | Easy — run each CTE as a standalone SELECT. | Hard — must extract inner subqueries manually. | Easy — SELECT * FROM temp_table at any point. |
| Side effects | None — pure, inline computation. | None — pure, inline computation. | Creates objects; requires cleanup. May cause locking in some engines. |
Connection to Advanced CTE Features
Once you are comfortable composing multiple non-recursive CTEs, the natural next step is to explore recursive CTEs and engine-specific materialization hints. Recursive CTEs use the same WITH syntax but allow a CTE to reference itself, enabling traversal of hierarchical data (organizational charts, bill-of-materials, graph adjacency lists). You can freely mix recursive and non-recursive CTEs in a single WITH block; simply add the RECURSIVE keyword after WITH (in PostgreSQL) or mark individual CTEs as recursive (in SQL Server via the anchor/recursive member pattern).
| Feature | Multiple (Non-Recursive) CTEs | Recursive CTEs |
|---|---|---|
| Self-reference | Not allowed—each CTE may only reference previously defined CTEs. | Required—the CTE references its own name in a UNION ALL. |
| Use case | Staged transformations, multi-source joins, ranked analytics. | Hierarchical queries, graph traversal, series generation. |
| Termination | Always terminates—no iteration. | Must guarantee termination via a base case and convergence condition. |
| Keyword | WITH cte AS (…) | WITH RECURSIVE cte AS (…) |
| Mixing | Can appear in a WITH RECURSIVE block alongside recursive CTEs. | Can appear alongside non-recursive CTEs. |
Modern analytics engineering frameworks like dbt (data build tool) take the multi-CTE philosophy further by encouraging developers to write each transformation as a separate model (SQL file), which dbt then compiles into a DAG of materialized views or tables. Understanding multi-CTE composition at the query level is the conceptual foundation for this model-level decomposition in production data pipelines.
Practice Problems
WITH a AS (…) WITH b AS (…) SELECT …?sales(sale_id, product_id, sale_date, quantity, price), write a query using two CTEs: the first computes total revenue (quantity × price) per product, and the second filters to products with total revenue exceeding 10,000. The final SELECT should return product_id and total_revenue, ordered by total_revenue descending.employees(emp_id, name, dept_id, salary) and departments(dept_id, dept_name). Write a query with three CTEs that: (1) computes the average salary per department, (2) identifies departments whose average salary exceeds the company-wide average, and (3) lists employees in those above-average departments along with their department name and how much their salary exceeds the department average.page_views(user_id, page, view_time) and conversions(user_id, conversion_time, revenue). Using multiple CTEs (fan-in pattern), write a query that computes: (a) the number of page views per user in the last 30 days, (b) the total conversion revenue per user in the last 30 days, and then joins these to produce a report showing user_id, view_count, total_revenue, and revenue_per_view. Include only users who have both page views and conversions.Summary — Multiple CTEs for Modular Query Design
Multiple CTEs enable you to decompose a complex SQL query into a sequence of named, self-documenting stages, each performing a single logical transformation. All CTEs share a single WITH keyword and are separated by commas. The key visibility rule is sequential: each CTE can reference only CTEs declared before it. This top-down ordering mirrors the flow of data through a pipeline and makes the query's intent transparent to reviewers.
The four primary composition patterns—linear pipeline, fan-in, fork, and hybrid—cover the vast majority of real-world analytical queries. Compared to nested subqueries, multiple CTEs offer superior readability and debuggability. Compared to temporary tables, they avoid side effects and allow the optimizer to apply global optimizations. Mastering multi-CTE composition is the foundation for recursive CTEs and modern analytics engineering practices.