CTE Referenced 3 Times — 50M Rows Scanned in SQL Server

Every production database eventually grows a query so complex it becomes a maintenance nightmare — subqueries nested inside subqueries, the same filter logic copy-pasted five times, and a WHERE clause that scrolls off the screen. CTEs were introduced precisely because real SQL work at scale demands readable, composable, and debuggable queries.

The core problem CTEs solve is scope and readability without sacrificing power. Before CTEs, your only option was derived tables (anonymous subqueries in FROM clauses) or views (which require DDL permissions and pollute your schema). CTEs give you a named, scoped, temporary result set that lives only for the duration of your query.

By the end of this article you'll write multi-step CTEs that replace tangled subquery nests, use recursive CTEs to walk org charts, understand exactly when your database materializes a CTE versus re-evaluates it, and avoid the three production gotchas that catch even experienced engineers off guard.

The Anatomy of a CTE — Structure, Scope and Why It Beats Subqueries

A CTE is defined with the WITH keyword, followed by a name, an optional column list, and the query body in parentheses. Everything after that closing parenthesis is the outer query. The CTE only exists for the lifetime of that single outer query execution.

You can define multiple CTEs in one WITH clause by separating them with commas. Crucially, a later CTE can reference an earlier one — this is what makes chaining possible. Think of it as a pipeline: each CTE refines the data one step further before handing it to the next stage.

Why does this beat a subquery? Readability is only part of the answer. The bigger win is debuggability. When you have a three-level nested subquery and something goes wrong, you can't easily isolate which level is producing bad data. With CTEs, you SELECT directly from each named stage during development — comment out the outer query and inspect intermediate results. That feedback loop is invaluable on complex data pipelines.

Also note: a CTE referenced multiple times in the outer query does NOT automatically get computed once and cached. Whether it's materialized or re-evaluated depends on your database engine — a critical performance detail covered in Section 3.

Recursive CTEs — Walking Trees, Org Charts and Hierarchical Data

A recursive CTE has two parts separated by UNION ALL: the anchor member (the base case — where the recursion starts) and the recursive member (which references the CTE itself to walk one level deeper). The engine executes the anchor once, then keeps executing the recursive member against the previous iteration's results until it returns zero rows.

The classic use case is an organizational hierarchy stored as an adjacency list — each employee row has a manager_id pointing to another employee_id. Without a recursive CTE, flattening that requires N queries for N levels. With recursive CTEs, one query handles any depth.

Cycles in data are the most dangerous edge case. If employee A is the manager of B and B is the manager of A (data corruption happens), a naive recursive CTE loops until it hits the depth limit. Always track visited IDs and break when you detect a repeat.

CTE Materialization vs. Re-evaluation — The Performance Truth Nobody Tells You

The SQL standard says nothing about whether a CTE must be materialized (computed once, stored) or re-evaluated (re-run every time it's referenced). Each database engine makes its own choice.

PostgreSQL prior to version 12 always materialized CTEs as optimization fences — the planner could not push WHERE predicates from the outer query into the CTE. PostgreSQL 12+ inlines non-recursive CTEs by default, allowing predicate pushdown. You can force materialization with the MATERIALIZED keyword.

SQL Server never guaranteed materialization — it treats CTEs as macro expansions. If you reference a CTE three times in a UNION ALL, SQL Server may execute that query three times. For expensive CTEs referenced repeatedly, load results into a #temp_table first.

MySQL 8.0+ inlines CTEs. Oracle materializes when it determines it's beneficial and accepts the WITH MATERIALIZE hint to force it.

Production CTE Patterns — Window Functions, Data Mutations and When Not to Use CTEs

CTEs compose beautifully with window functions because window function results can't be filtered in the same SELECT where they're computed — you need a wrapper, and a CTE is cleaner than a subquery for this.

CTEs also work with INSERT, UPDATE, DELETE and MERGE in PostgreSQL and SQL Server — called data-modifying CTEs. In PostgreSQL, data-modifying CTEs execute exactly once, and their effects are visible to the outer query but not to other CTEs in the same WITH clause.

Know when NOT to use a CTE. If your query is a single flat SELECT that runs fine as a subquery, wrapping it in a CTE adds no value. CTEs shine for multi-step logic, recursion, and avoiding repeated complex expressions. A CTE over a 50-million-row unindexed table is still a full table scan — CTEs don't fix missing indexes.

Frequently Asked Questions