CTE SQL Guide: Syntax, Recursion, and Refactoring

What is a CTE in SQL?

A CTE is primarily a query-structure tool, not a promise to store data or improve performance. It can make grain, dependencies, and transformations visible, but a poorly designed chain of CTEs can merely spread complexity across more names. Each stage still needs a clear purpose, input grain, output grain, and validation rule.

WITH completed_orders AS (
  SELECT order_id, customer_id, order_date, amount
  FROM orders
  WHERE status = 'completed'
)
SELECT customer_id, SUM(amount) AS revenue
FROM completed_orders
GROUP BY customer_id;

Read a CTE as a named query stage

The WITH clause appears before the main statement. Each CTE has a name, an optional output-column list, and a query body. Later CTEs can usually reference earlier CTEs in the same clause, forming a directed dependency chain. The final SELECT, INSERT, UPDATE, DELETE, or vendor-supported statement consumes one or more of those results.

Design multiple CTEs as a dependency graph

Multiple CTEs are separated by commas under one WITH. Use them to reveal meaningful transformations: define the eligible population, reduce one-to-many facts, attach dimensions, calculate a window result, then shape the final output. Avoid a long serial chain where each stage adds only one cosmetic expression; excessive fragmentation forces readers to jump between names without reducing conceptual load.

WITH eligible_orders AS (
  SELECT order_id, customer_id, order_date, amount
  FROM orders
  WHERE status = 'completed'
),
customer_totals AS (
  SELECT customer_id,
         COUNT(*) AS order_count,
         SUM(amount) AS revenue
  FROM eligible_orders
  GROUP BY customer_id
),
ranked_customers AS (
  SELECT customer_id, order_count, revenue,
         DENSE_RANK() OVER (
           ORDER BY revenue DESC
         ) AS revenue_tier
  FROM customer_totals
)
SELECT *
FROM ranked_customers
WHERE revenue_tier <= 10;

The stage names communicate a contract. eligible_orders is one row per completed order; customer_totals is one row per customer; ranked_customers preserves that customer grain and adds a tier. Reviewers can test each transition rather than reasoning through one deeply nested expression.

Build a recursive CTE with a safe stopping rule

A recursive CTE has an anchor term that creates the initial rows and a recursive term that joins new rows to the prior result. A set operator—commonly UNION ALL—combines them. Evaluation continues until the recursive term produces no new rows or a database-specific recursion boundary stops it.

WITH RECURSIVE org_tree AS (
  SELECT employee_id, manager_id, employee_name,
         0 AS depth,
         ARRAY[employee_id] AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.manager_id, e.employee_name,
         t.depth + 1,
         t.path || e.employee_id
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.employee_id
  WHERE NOT e.employee_id = ANY(t.path)
    AND t.depth < 100
)
SELECT * FROM org_tree;

This PostgreSQL-style example records a path to reject cycles and adds a defensive depth boundary. Exact array, cycle-detection, and recursion syntax varies by platform. The PostgreSQL WITH-query documentation explains recursive evaluation, search order, cycle handling, and materialization controls. Always test self-loops, longer cycles, orphan nodes, multiple roots, deep chains, and duplicate edges.

Choose between a CTE, subquery, view, and temp table

These constructs can express similar transformations but have different scope and operational behavior. Choose by reuse, observability, statistics, lifecycle, permissions, and portability—not by a blanket claim that one is always faster.

A compact subquery may be clearer than a one-line CTE used once. A temp table may be better when a costly intermediate result is reused many times and needs statistics or indexes. A view may formalize governed logic shared across teams. CTEs excel when the logic belongs to one statement and named stages materially improve reasoning.

Refactor nested SQL into meaningful CTE stages

Do not mechanically turn every pair of parentheses into a CTE. First identify the decision population and current output grain. Then mark transformations that change eligibility, row count, grain, or meaning. Those boundaries are strong candidates for named stages.

1. Freeze expected behavior. Save representative inputs, expected rows, totals, null behavior, and tie rules.
2. Map the nested query. Identify joins, filters, aggregates, windows, correlated references, and repeated expressions.
3. Name semantic boundaries. Extract stages such as eligible events, daily totals, latest records, or ranked candidates.
4. Project only needed columns. Narrow each contract so later stages cannot accidentally depend on irrelevant data.
5. Validate every boundary. Compare counts and business keys against the original query and independent expectations.
6. Compare execution evidence. Review actual plans and benchmark representative workloads before claiming improvement.

Recognize CTE anti-patterns before they spread

CTEs improve readability only when their boundaries match reasoning boundaries. A stage named data that selects every column, joins five tables, calculates several metrics, and filters unrelated populations is still a monolith. Conversely, splitting each expression into a separate one-line CTE creates navigational overhead without adding a meaningful contract.

Another warning sign is a CTE used as an informal security boundary. Unless the database feature and permissions explicitly guarantee isolation, later query logic may still expose columns or rows. Access control belongs in governed database policies, secure views, or authorized layers—not in a naming convention.

Finally, do not preserve a CTE merely because it once fixed a plan. Engine upgrades, statistics, parameter distributions, and schema changes can invalidate that outcome. Keep performance-sensitive structure backed by reproducible benchmarks and comments that state the observed reason, tested version, and fallback—not folklore.

Do not assume a CTE is materialized or faster

CTE optimization behavior differs across databases and versions. Some engines inline eligible nonrecursive CTEs into the parent query, allowing predicates and joins to be optimized together. Some materialize under specific conditions or hints. A CTE referenced multiple times may be recalculated, shared, spooled, or transformed. Recursive CTEs have different execution needs from nonrecursive ones.

Read the documentation for the exact engine and version. For SQL Server syntax and usage rules, see Microsoft's common table expression reference. Treat plans, runtime metrics, and result equivalence as evidence; treat “CTEs are faster” or “CTEs are always optimization fences” as unsafe generalizations.

Validate a CTE query before production

• Check that every stage has a semantic name and documented row grain.
• Compare total rows, distinct keys, null rates, and aggregates at every stage boundary.
• Confirm filters are applied before or after ranking and aggregation according to the business question.
• For recursion, test cycles, orphans, duplicate edges, multiple roots, maximum depth, and termination.
• Inspect repeated references, wide projections, sorts, spills, and estimated versus actual rows.
• Compare the refactored query against expected outcomes, not only against a potentially wrong original.

CTE SQL frequently asked questions

Continue with a related guide

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents.

• SQL subqueries
• Nested SQL queries
• Correlated subqueries