What Is a CTE? Common Table Expressions Explained

A common table expression, or CTE, is a named temporary result set defined at the top of a SQL query with a WITH clause, which you can then reference like a table for the rest of that query. It exists only for the duration of the query that defines it — nothing is written to disk, and no separate object needs to be created or dropped. CTEs exist mainly to make complex queries readable, and in one specific form, to do something a plain query can’t: recurse.

Basic syntax

A CTE is defined with WITH name AS (subquery), then referenced by name in the main query:

WITH high_value_orders AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 1000
)
SELECT customers.name, high_value_orders.total
FROM customers
JOIN high_value_orders ON customers.id = high_value_orders.customer_id;

Without the CTE, this would be written as a subquery inline in the FROM or JOIN clause — functionally similar in most databases, but harder to read once you nest more than one or two levels. The CTE gives the intermediate result a name, so the final query reads top-to-bottom instead of inside-out.

You can chain multiple CTEs in one WITH clause, each able to reference the ones defined before it:

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
), monthly_totals AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS total
  FROM paid_orders
  GROUP BY 1
)
SELECT * FROM monthly_totals ORDER BY month;

This is the main practical benefit: breaking a query into named, sequential steps instead of a wall of nested subqueries. It reads closer to how you’d explain the logic out loud.

Recursive CTEs

The one thing a CTE can do that a subquery genuinely cannot is recurse — reference itself to walk a hierarchy or generate a sequence. A recursive CTE has two parts joined by UNION ALL: an anchor (base case) and a recursive term that references the CTE’s own name.

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, org_chart.depth + 1
  FROM employees e
  JOIN org_chart ON e.manager_id = org_chart.id
)
SELECT * FROM org_chart ORDER BY depth;

This walks a manager-report tree from the top down, one level per iteration, until no more matching rows are found. Recursive CTEs are the standard SQL way to handle hierarchical data — org charts, category trees, bill-of-materials structures, or graph traversal within a relational database — without pulling everything into application code and walking it there.

CTE vs subquery vs temp table

	Subquery	CTE	Temp table
Named	No	Yes	Yes
Reusable in same query	No, must repeat	Yes, reference by name	Yes
Persists past the query	No	No	Yes, until session/transaction ends
Can recurse	No	Yes (WITH RECURSIVE)	No, needs a loop
Optimizer treatment	Inlined	Varies by database — inlined or materialized	Materialized, indexable

A plain subquery and a non-recursive CTE are often equivalent in what they can express — the difference is readability, not capability. Some databases (PostgreSQL since version 12, for instance) will inline a simple CTE into the surrounding query, so it isn’t automatically an “optimization fence” the way it sometimes was historically. If you need to reuse an expensive intermediate result across many separate queries, a real temp table is a better fit than a CTE, since a CTE only lives for one statement.

When to reach for one

Use a CTE when a query has grown enough nested subqueries that following the logic requires re-reading it twice, when the same subquery would otherwise be repeated in multiple places, or when you need actual recursion over hierarchical data. For simple one-off filters, a plain WHERE clause or a single subquery is often just as clear without the extra WITH block.

CTEs pair naturally with other query-structuring tools — see SQL window functions for running totals and rankings, and SQL joins for combining CTEs with the rest of your schema. If a query built from CTEs runs slower than expected, check database indexing on the underlying tables first — a CTE doesn’t change what indexes the planner can use on the base tables it reads from.

The takeaway

A CTE is a named, query-scoped result set defined with WITH — it exists to make multi-step SQL readable and, in its recursive form, to walk hierarchical data that a plain query can’t traverse. For non-recursive cases it’s mostly a readability tool rather than a performance one; reach for it when nested subqueries have made a query hard to follow, and reach for WITH RECURSIVE when you need to walk a tree or graph inside the database itself.