What is a common table expression (CTE) in SQL?
A common table expression (CTE) is a named, temporary result set that you define at the top of a query with a WITH clause and then reference like a regular table anywhere in the statement that follows. CTEs make a long query readable by giving each intermediate step a name, and a recursive CTE (WITH RECURSIVE) can walk a hierarchy — an org chart, a category tree, a thread of replies — that a plain SELECT cannot express. On Postgres, a non-recursive CTE is usually inlined into the plan like a subquery, so using one is rarely a cost — it is a readability tool first.
1.Write a basic CTE
Define the CTE with WITH name AS (query), then select from it by name. This is the same result as nesting the same SELECT as a subquery, but named and easier to read.
with big_orders as (
select customer_id, total_cents
from orders
where total_cents > 10000
)
select customer_id, count(*) as order_count
from big_orders
group by customer_id;2.Chain multiple CTEs
A later CTE can reference an earlier one, which turns a multi-step report into a readable pipeline instead of one deeply nested query.
with monthly_revenue as (
select date_trunc('month', created_at) as month, sum(total_cents) as cents
from orders
group by 1
),
monthly_growth as (
select month, cents,
cents - lag(cents) over (order by month) as change_cents
from monthly_revenue
)
select * from monthly_growth order by month;3.Recursive CTEs with WITH RECURSIVE
WITH RECURSIVE has a base case (the anchor, run once) unioned with a recursive term that references the CTE itself and runs again on each new batch of rows, stopping when a round returns nothing. This is the standard way to walk a parent/child tree in Postgres, such as every employee under a given manager.
with recursive org_chart as (
-- anchor: the top of the tree
select id, name, manager_id, 1 as depth
from employees
where manager_id is null
union all
-- recursive term: next level down, joined to the previous batch
select e.id, e.name, e.manager_id, oc.depth + 1
from employees e
join org_chart oc on e.manager_id = oc.id
)
select * from org_chart order by depth, name;4.Performance notes
Since Postgres 12, a non-recursive CTE is inlined into the surrounding query plan by default — it is no longer an "optimization fence" — so the planner can push filters and joins into it the same as a subquery. Force the old fenced behavior with "as materialized" when you deliberately want the CTE computed once and reused, or force inlining with "as not materialized". A recursive CTE cannot be inlined: it always materializes the working table, so put a selective filter in the anchor term rather than filtering the final result, index the columns the recursive join uses (manager_id above), and add a depth or visited-set check if the data can contain a cycle, since an un-terminated recursive CTE runs until it is stopped. Try these queries on a free Scribase Postgres project to see the plan with EXPLAIN ANALYZE against your own data.
Common questions
What is a common table expression (CTE) in SQL?
A CTE is a named, temporary result set defined with a WITH clause at the start of a query. You reference it by name later in the same statement, like a table, which makes multi-step logic readable without nesting subqueries.
Is a CTE the same as a temporary table?
No. A CTE only exists for the duration of the single statement that defines it and is not stored — nothing persists after the query finishes. A temporary table is a real table in a temp schema that persists for the session or transaction.
Does Postgres run a CTE once or every time it is referenced?
It depends. Since Postgres 12, a non-recursive CTE is inlined by default, so it can be planned and executed as if it were a subquery wherever it is referenced. Add "as materialized" to force it to run once and be reused every time it is referenced.
What makes WITH RECURSIVE different from a regular CTE?
A regular CTE runs once. WITH RECURSIVE repeatedly executes a recursive term against the rows the previous round produced, starting from an anchor (base case), until a round returns no rows — which is how it walks a tree or graph of arbitrary depth.
Should I use a CTE or a subquery?
Use whichever reads more clearly; on current Postgres a non-recursive CTE and an equivalent subquery usually produce the same plan. Reach for a CTE when you have several named steps to chain, and for WITH RECURSIVE specifically when you need to walk a hierarchy that a plain subquery cannot express.
Ready to try it?
Start a free project and follow this guide against a real backend, or move an existing project in with a dry run first.