scribase
Guide

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.

Published November 16, 20267 min read

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.

sql
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.

sql
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.

sql
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.

FAQ

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.