Common Table Expressions (CTEs) — Write a CTE with WITH to structure complex queries - SQL

SECTION 1

Historical Context & Motivation

Relational databases grew out of Edgar F. Codd's landmark 1970 paper, and for three decades the dominant language for querying them — Structured Query Language (SQL) — relied on inline subqueries whenever a developer needed to reference an intermediate result set. These nested subqueries could become deeply indented and difficult to maintain; a single query might contain three or four levels of nesting, each referencing the one above, creating a style that practitioners sometimes called "spaghetti SQL." The need for a cleaner abstraction was evident long before the standard committee acted on it.

The concept of a Common Table Expression (CTE) was introduced to let developers define temporary, named result sets that exist only for the duration of a single statement. The idea borrowed from functional programming, where giving a name to an intermediate computation improves both readability and reasoning. As database engines matured, the CTE also became the vehicle for expressing recursive queries — enabling tree and graph traversals directly in SQL without procedural code.

1970 • Codd's Relational Model

Edgar F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," laying the theoretical groundwork for SQL and relational algebra.

1986 • SQL-86 Standard

The first ANSI SQL standard is ratified. Queries rely exclusively on inline subqueries and joins; no mechanism exists for naming intermediate result sets.

1999 • SQL:1999 — CTEs Introduced

The SQL:1999 (SQL3) standard introduces the WITH clause, supporting both non-recursive and recursive CTEs. This marks a paradigm shift in query composition.

2005–2012 • Major RDBMS Adoption

PostgreSQL (8.4, 2009), SQL Server (2005), Oracle (9i R2, 2002), and MySQL (8.0, 2018) each ship production-ready CTE support, driving widespread adoption.

2020s • CTEs Become Idiomatic

Modern SQL style guides and analytics frameworks (dbt, Looker) recommend CTEs as the primary mechanism for structuring complex analytical queries.

The central question the CTE addresses is straightforward: how can we decompose a complex query into named, logically sequential steps without resorting to temporary tables or deeply nested subqueries? The WITH clause is SQL's answer, and understanding it is essential for writing maintainable, performant database code in any modern RDBMS.

SECTION 2

Core Principles & Definitions

A Common Table Expression is a named, temporary result set defined at the beginning of a SQL statement using the WITH keyword. The CTE exists only within the scope of the immediately following SELECT, INSERT, UPDATE, or DELETE statement — once that statement finishes execution, the CTE ceases to exist. This is fundamentally different from a temporary table or a view, both of which persist beyond a single statement. Understanding the CTE requires grasping a handful of foundational ideas.

Statement-Scoped Lifetime

A CTE is born when the WITH clause is parsed and dies when the enclosing statement completes. It is not stored in the database catalog and cannot be referenced by subsequent, independent statements.

Named Abstraction

Each CTE is given an alias (e.g., monthly_totals) that can be referenced in the main query or in later CTEs exactly like a table name, enabling a top-down, stepwise reading of the logic.

Composability via Chaining

Multiple CTEs can be defined in a single WITH clause, separated by commas. Later CTEs may reference earlier ones, forming a pipeline of transformations that the final SELECT draws upon.

Recursion Support

A recursive CTE (declared with WITH RECURSIVE) can reference itself, enabling traversal of hierarchical or graph-structured data such as org charts and bill-of-materials trees.

Optimizer Transparency

Most modern engines inline non-recursive CTEs during optimization, meaning they are logically equivalent to subqueries. Some engines (e.g., PostgreSQL ≥ 12) provide MATERIALIZED / NOT MATERIALIZED hints for finer control.

✦

KEY TAKEAWAY

Think of a CTE like a local variable in a function. Just as you might write filtered = df[df.status == 'active'] in Python before passing filtered to the next operation, a CTE lets you name an intermediate result set so the rest of the query reads linearly from top to bottom rather than inside out.

SECTION 3

Visual Explanation — Anatomy of a CTE

The diagram shows the three structural layers of a CTE-based query: the WITH keyword and CTE alias (purple), the CTE body query (cyan), a second chained CTE (pink), and the final SELECT (amber) that consumes the named result sets. The dashed bracket on the left emphasizes that all CTEs share the same statement scope.

Notice how the query reads top-to-bottom like imperative code: first we define cte_name to aggregate salaries by department, then enriched joins that result with department metadata, and finally the main SELECT filters the enriched data. Without CTEs, this same logic would require either nested subqueries (reading inside-out) or explicit temporary tables (requiring additional DDL and cleanup). The CTE achieves both clarity and conciseness within a single, self-contained statement.

SECTION 4

How CTEs Work Under the Hood

Understanding CTE behavior requires examining what the query optimizer does with the WITH clause. In most modern engines, a non-recursive CTE is treated as syntactic sugar: the optimizer inlines the CTE body wherever the CTE name appears, then applies standard optimization passes — predicate pushdown, join reordering, index selection — on the unified query tree. This means a non-recursive CTE is, from a performance perspective, generally equivalent to a derived table (inline subquery in the FROM clause). There are, however, important engine-specific nuances.

Non-Recursive CTE Syntax

NON-RECURSIVE CTE TEMPLATE

WITH cte_alias [(col₁, col₂, …)] AS ( SELECT … ) SELECT … FROM cte_alias …;

cte_alias — a valid SQL identifier serving as the temporary table name. (col₁, col₂, …) — optional explicit column aliases; if omitted, column names are inherited from the inner SELECT. AS ( SELECT … ) — the CTE body, which must be a valid query expression. The parentheses are mandatory.

Chained CTEs

CHAINED CTE TEMPLATE

WITH A AS ( SELECT … ), B AS ( SELECT … FROM A ), C AS ( SELECT … FROM A JOIN B … ) SELECT … FROM C;

Each subsequent CTE may reference any previously defined CTE within the same WITH block. The order of definition matters — a CTE cannot forward-reference one that has not yet been declared. The commas between CTEs replace additional WITH keywords.

Recursive CTE Structure

RECURSIVE CTE TEMPLATE

WITH RECURSIVE r AS ( SELECT … -- anchor member UNION ALL SELECT … FROM r WHERE … -- recursive member ) SELECT * FROM r;

Anchor member — the base case, executed once. Recursive member — references the CTE itself; executed repeatedly until it returns an empty set. UNION ALL concatenates each iteration's rows into the final result. A termination condition in the WHERE clause is essential to prevent infinite loops.

ℹ

Performance Note

PostgreSQL versions prior to 12 materialized every CTE into a temporary work table, acting as an optimization fence that prevented predicate pushdown. Starting with version 12, non-recursive CTEs referenced only once are inlined by default. You can force behavior with AS MATERIALIZED (...) or AS NOT MATERIALIZED (...). In SQL Server and MySQL, non-recursive CTEs have always been inlined.

SECTION 5

Recursive CTEs — Traversing Hierarchies

One of the most powerful capabilities unlocked by the CTE is recursion. In many real-world databases, data is inherently hierarchical — organizational charts, file system paths, category taxonomies, and bill-of-materials structures all form trees or directed acyclic graphs. Before recursive CTEs, traversing these structures required either application-level loops or vendor-specific extensions like Oracle's CONNECT BY. The WITH RECURSIVE clause provides a portable, standard-compliant solution.

Left: an organizational hierarchy stored in a self-referencing employees table. Right: the recursive CTE's execution stages — the anchor returns the CEO, then each recursive iteration discovers the next level of subordinates until no new rows are produced, at which point the engine terminates the recursion and returns the accumulated result.

The execution model shown above is sometimes called iterative fixpoint evaluation. The engine maintains a working table for the current iteration and an intermediate table accumulating all results. At each step, the recursive member query runs against the working table; its output replaces the working table for the next round and is also appended to the intermediate table. When the working table becomes empty, the fixpoint has been reached and the intermediate table becomes the CTE's result. It is important to include a termination condition — typically a WHERE depth < N or a join predicate that eventually produces no matches — because an unbounded recursion will either run until the engine's recursion limit (e.g., max_recursive_iterations in PostgreSQL, default 100) or exhaust memory.

SECTION 6

Worked Example — Monthly Revenue Pipeline

Suppose we have an e-commerce database with two tables: orders(order_id, customer_id, order_date, total_amount) and customers(customer_id, name, region). The business question is: For each region, show the monthly revenue and label months where revenue exceeded the region's 12-month average as "above average." This naturally decomposes into three logical stages: aggregation, averaging, and comparison.

Multi-CTE Revenue Analysis

1
Step 1 — Define the monthly revenue CTE

We begin by writing a CTE called monthly_rev that joins orders with customers and aggregates revenue by region and month: WITH monthly_rev AS ( SELECT c.region, DATE_TRUNC('month', o.order_date) AS month, SUM(o.total_amount) AS revenue FROM orders o JOIN customers c USING (customer_id) GROUP BY c.region, DATE_TRUNC('month', o.order_date) )

Result: one row per (region, month) pair with aggregated revenue.
2
Step 2 — Compute the rolling 12-month average per region

A second CTE, region_avg, references monthly_rev and uses a window function to compute a trailing average: , region_avg AS ( SELECT region, month, revenue, AVG(revenue) OVER ( PARTITION BY region ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW ) AS avg_12m FROM monthly_rev )

Result: each row now carries both the month's revenue and its region's trailing 12-month average.
3
Step 3 — Label and filter in the main SELECT

The final SELECT reads from region_avg and applies a CASE expression to classify each month: SELECT region, month, revenue, ROUND(avg_12m, 2) AS avg_12m, CASE WHEN revenue > avg_12m THEN 'above average' ELSE 'at or below' END AS performance FROM region_avg ORDER BY region, month;

Final output: a clean table with region, month, revenue, 12-month average, and a performance label — all built from two composable CTEs.
4
Step 4 — Review the complete query

The full query reads top-to-bottom like a data pipeline: aggregate → enrich → present. Contrast this with the equivalent nested-subquery version, which would require two levels of nesting in the FROM clause — one subquery for aggregation wrapped inside another for the window function — making the intent far harder to discern during code review.

💡

DESIGN HEURISTIC

If you find yourself nesting more than one subquery, consider refactoring into chained CTEs. Each CTE should represent a single, clearly named transformation — much like each function in a well-factored codebase should have a single responsibility.

SECTION 7

CTEs vs. Alternatives — When to Use What

CTEs are not the only way to decompose complex queries. SQL offers several related mechanisms — inline subqueries (derived tables), views, and temporary tables — each with its own trade-offs. Choosing the right tool depends on factors like reuse scope, performance implications, and whether you need the result set to persist beyond a single statement.

Criterion	CTE (WITH)	Derived Table (Subquery)	Temp Table	View
Scope	Single statement	Single FROM clause	Session or transaction	Persistent in catalog
Readability	High — top-to-bottom	Low — inside-out nesting	Medium — requires DDL	High — but definition elsewhere
Reusability in query	Alias reusable multiple times	Must repeat subquery text	Table exists for session	Globally accessible
Recursion	Yes (WITH RECURSIVE)	No	No (requires procedural loop)	No
Optimization	Inlined or materialized (engine-dependent)	Always inlined	Materialized by definition	Inlined (unless materialized view)
Index support	No — virtual result set	No	Yes — CREATE INDEX	Only with materialized views

Comparison of SQL query decomposition strategies

🔧

WHEN TO REACH FOR EACH TOOL

Use a CTE when you need readable, composable logic within one statement — especially if recursion is involved. Use a temp table when the intermediate result is large and accessed multiple times across different statements — the materialized data and potential for indexing will pay off. Use a view when the abstraction should be permanent and shared across the application.

SECTION 8

Connection to Advanced SQL Patterns

The CTE is not merely a convenience for readability — it serves as a gateway to several advanced SQL patterns that are increasingly important in data engineering and analytics workflows. Understanding CTEs prepares you for topics such as recursive graph algorithms, modular analytics via dbt, and writable CTEs for complex DML (data manipulation).

Foundational CTE Concept	Advanced Extension
Non-recursive CTE for readability	dbt models — each model is essentially a named CTE that compiles to a view or table, enabling software-engineering practices (version control, testing) in SQL analytics.
Recursive CTE for tree traversal	Recursive graph queries — transitive closure, shortest path (with cycle detection via arrays), and topological sorting. PostgreSQL's ltree extension complements these patterns.
Chained CTEs as a pipeline	Writable CTEs (PostgreSQL) — INSERT, UPDATE, or DELETE inside a CTE body using RETURNING, then consuming those modified rows in the main SELECT for audit logging or cascading operations.
`MATERIALIZED` hint	Query plan tuning — strategically materializing expensive CTEs to avoid redundant computation when the CTE is referenced multiple times, or forcing inlining when the CTE is cheap and predicate pushdown is beneficial.

From foundational CTEs to advanced SQL engineering

As you advance in your database curriculum, you will encounter window functions combined with CTEs to express running totals, rank-based filtering, and sessionization queries. The mental model is always the same: break the problem into named stages, test each stage independently, then compose. This mirrors the decomposition principle you already apply when writing functions in Python or Java, and it scales well to production SQL codebases with hundreds of lines per query.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain, in your own words, the key difference between a CTE and a temporary table. Under what circumstances would each be preferred?

Show answer

A CTE is a named result set that exists only for the duration of the single SQL statement in which it is defined; it is not materialized to disk (unless the optimizer or a hint dictates otherwise) and requires no explicit cleanup. A temporary table, in contrast, persists for the duration of a session or transaction, is physically stored, and can be indexed. A CTE is preferred when readability within one query is the goal and the intermediate data is not enormous. A temporary table is preferred when the intermediate result must be reused across multiple independent statements, when it is large enough that indexing would speed subsequent joins, or when the query optimizer benefits from statistics gathered on the materialized data.

Question 2: BASIC

Given a table products(product_id, category, price), write a CTE called expensive that selects all products with a price above 100, and then write a main query that counts the number of expensive products per category.

Show answer

The solution is:

WITH expensive AS ( SELECT product_id, category, price FROM products WHERE price > 100 ) SELECT category, COUNT(*) AS cnt FROM expensive GROUP BY category ORDER BY cnt DESC;

The CTE expensive filters rows before the aggregation, and the main SELECT groups the filtered set by category. This is logically equivalent to placing the filter in a WHERE clause of the outer query, but the CTE version is clearer when additional transformations are added later.

Question 3: INTERMEDIATE

You have tables orders(order_id, customer_id, order_date, total) and customers(customer_id, name). Using two chained CTEs, write a query that (1) computes each customer's total lifetime spending, and (2) returns only those customers whose lifetime spending exceeds the overall average lifetime spending, sorted descending by spending.

Show answer

The query uses two CTEs:

WITH lifetime AS ( SELECT customer_id, SUM(total) AS total_spent FROM orders GROUP BY customer_id ), avg_spend AS ( SELECT AVG(total_spent) AS avg_val FROM lifetime ) SELECT c.name, l.total_spent FROM lifetime l JOIN customers c USING (customer_id) CROSS JOIN avg_spend a WHERE l.total_spent > a.avg_val ORDER BY l.total_spent DESC;

The first CTE aggregates spending per customer. The second CTE computes the scalar average from the first CTE's output. The main query joins these results with the customer name and filters to above-average spenders. This three-stage pipeline avoids repeating the aggregation subquery and keeps each concern cleanly separated.

Question 4: APPLIED

An employees table has columns (emp_id, name, manager_id) where manager_id references emp_id (NULL for the CEO). Write a recursive CTE that produces each employee's name, their depth in the hierarchy (CEO = 0), and the full management chain as a path string (e.g., 'CEO → VP Eng → Dev A').

Show answer

A recursive CTE solves this:

WITH RECURSIVE org AS ( SELECT emp_id, name, 0 AS depth, name AS path FROM employees WHERE manager_id IS NULL  UNION ALL  SELECT e.emp_id, e.name, o.depth + 1, o.path || ' → ' || e.name FROM employees e JOIN org o ON e.manager_id = o.emp_id ) SELECT name, depth, path FROM org ORDER BY depth, name;

The anchor member selects the CEO (manager_id IS NULL) with depth 0 and initializes the path. Each recursive iteration joins the current working set with employees whose manager_id matches, incrementing depth and concatenating the name onto the path. Recursion terminates naturally when no more children exist. Note the use of || for string concatenation (standard SQL); in MySQL you would use CONCAT().

Question 5: CRITICAL THINKING

Consider a CTE that is referenced three times in the main SELECT (once in a subquery, once in a JOIN, and once in a WHERE EXISTS). Discuss how different database engines might handle this from an optimization perspective. Under what conditions would you add a MATERIALIZED hint, and under what conditions would NOT MATERIALIZED be more appropriate? Justify your reasoning with reference to predicate pushdown, computation cost, and result set size.

Show answer

When a CTE is referenced multiple times, the optimizer faces a trade-off. If the CTE body is expensive to compute (e.g., involves large aggregations or joins) and produces a relatively small result set, materializing it into a temporary spool avoids redundant computation — the three references all read from the spool. In this scenario, MATERIALIZED is beneficial. Conversely, if the CTE body is cheap (perhaps a simple filter on an indexed column) and produces many rows, materializing it would waste memory and prevent the optimizer from pushing predicates from the outer query into the CTE body. For example, a WHERE clause in the main query that restricts the CTE's output to a single row could allow an index seek if inlined, but would force a full scan of the materialized spool. In PostgreSQL ≥ 12, a multi-referenced non-recursive CTE is materialized by default; you should add NOT MATERIALIZED when predicate pushdown or index utilization is more valuable than avoiding recomputation. SQL Server always inlines non-recursive CTEs, so this hint does not apply there. The key principle is to examine the EXPLAIN plan: if you see 'CTE Scan' with a large row estimate and a filter applied after materialization, inlining is likely better.

SUMMARY

Lesson Summary

Summary

A Common Table Expression (CTE) is defined using the WITH clause and provides a named, statement-scoped result set that improves the readability and maintainability of complex SQL queries. Multiple CTEs can be chained together in a single WITH block, with later CTEs referencing earlier ones, forming a top-to-bottom data pipeline. For hierarchical or graph-structured data, recursive CTEs (declared with WITH RECURSIVE) use an anchor member and a recursive member connected by UNION ALL to iteratively build up results until a fixpoint is reached.

From an optimization perspective, most engines inline non-recursive CTEs like derived tables, so performance is typically equivalent to subqueries. When a CTE is referenced multiple times or is expensive to compute, consider the MATERIALIZED hint (PostgreSQL ≥ 12) to avoid redundant work. CTEs complement — rather than replace — temporary tables (for cross-statement reuse and indexing) and views (for persistent, shared abstractions). Mastering CTEs is the foundation for advanced patterns including writable CTEs, dbt-style modular analytics, and recursive graph algorithms in SQL.