CTE SQL: When a WITH Clause Helps and When It Quietly Costs You

A common table expression is a named temporary result set defined with WITH and referenced by the statement that follows it, and its main job is making a complicated query readable rather than making it faster.

That last part is worth saying early, because CTEs are often introduced as an optimisation and they are not one. On most engines a simple CTE compiles to the same plan as the equivalent subquery. On some, in some versions, it compiles to something considerably worse.

What they genuinely buy you is a query a colleague can read, a self-referencing form that nothing else can express, and a way to reference the same intermediate result more than once without repeating it.

The basic form
Recursive CTEs, which nothing else replaces
The optimisation fence, which is the real trap
Reading the plan rather than guessing
When to reach for one
How this fits the rest of the stack
FAQ

The basic form

A CTE is defined before the query that uses it, and reads top to bottom, which is the whole point.

WITH recent_orders AS (
    SELECT customer_id, SUM(total) AS spend, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY customer_id
)
SELECT c.name, r.spend, r.order_count
FROM recent_orders r
JOIN customers c ON c.id = r.customer_id
WHERE r.spend > 1000
ORDER BY r.spend DESC;

Compare that against the same logic as a nested subquery in the FROM clause. Identical result, and the reader has to start in the middle and work outward. The CTE version defines its terms first, which is how people read.

You can define several, separated by commas, and later ones can reference earlier ones:

WITH active_customers AS (
    SELECT id, name FROM customers WHERE status = 'active'
),
their_orders AS (
    SELECT o.customer_id, SUM(o.total) AS spend
    FROM orders o
    JOIN active_customers a ON a.id = o.customer_id
    GROUP BY o.customer_id
)
SELECT a.name, t.spend
FROM active_customers a
JOIN their_orders t ON t.customer_id = a.id;

Note the syntax detail that catches people: WITH appears once, and subsequent expressions are comma-separated without repeating the keyword.

Recursive CTEs, which nothing else replaces

This is the capability that makes CTEs genuinely necessary rather than merely tidy. A recursive CTE references itself, which lets you walk a hierarchy of unknown depth in one query.

WITH RECURSIVE org_chart AS (
    -- Anchor: where the walk starts.
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive term: joins back to the CTE itself.
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
    WHERE oc.depth < 20          -- depth guard, see below
)
SELECT depth, name FROM org_chart ORDER BY depth, name;

The structure is fixed: an anchor query, UNION ALL, then a term that joins back to the CTE’s own name. The engine runs the anchor, then repeatedly runs the recursive term against the rows produced last round, until a round produces nothing.

The depth guard is not optional in practice. If your data contains a cycle, an employee who is somehow their own manager through a chain, the query runs until it exhausts memory or your patience. A depth limit turns an outage into a truncated result you can investigate.

For genuine cycle detection rather than a blunt limit, carry the path and check membership:

WITH RECURSIVE walk AS (
    SELECT id, name, manager_id, ARRAY[id] AS path
    FROM employees WHERE manager_id IS NULL
  UNION ALL
    SELECT e.id, e.name, e.manager_id, w.path || e.id
    FROM employees e
    JOIN walk w ON e.manager_id = w.id
    WHERE NOT e.id = ANY(w.path)    -- stop before revisiting
)
SELECT * FROM walk;

Note that the keyword is WITH RECURSIVE in Postgres, MySQL and SQLite, while SQL Server uses plain WITH and infers recursion from the self-reference. Same concept, different spelling.

The optimisation fence, which is the real trap

Here is the behaviour that turns a readability improvement into a performance regression, and it is the single most important thing to know about CTEs.

On PostgreSQL before version 12, a CTE was always materialised: computed in full, stored, and then used. The planner could not push a predicate from the outer query into the CTE. So this:

WITH all_orders AS (
    SELECT * FROM orders          -- ten million rows
)
SELECT * FROM all_orders WHERE id = 12345;

…materialised ten million rows and then found one, where the equivalent subquery would have used the index and read a single row. This caught a great many people and was a well-known footgun.

Postgres 12 changed the default: a CTE referenced once and free of side effects is now inlined, so the planner can optimise across the boundary. The old behaviour is still available explicitly, and is sometimes what you want:

-- Force materialisation: compute once, reuse.
WITH expensive AS MATERIALIZED (
    SELECT customer_id, expensive_calculation(data) AS score FROM events
)
SELECT * FROM expensive WHERE score > 90
UNION ALL
SELECT * FROM expensive WHERE score < 10;

-- Force inlining, even if referenced several times.
WITH cheap AS NOT MATERIALIZED ( SELECT ... ) SELECT ...;

The rule of thumb: materialise when the CTE is expensive and referenced more than once, inline otherwise. If you are on an older Postgres, or on an engine with a materialising planner, check the plan rather than assuming.

MySQL 8 merges or materialises depending on the query. SQL Server treats a CTE as a macro and expands it inline every time it is referenced, which means a CTE referenced three times is evaluated three times, the opposite trap.

Reading the plan rather than guessing

Every claim above is engine-specific and version-specific, which means the only reliable answer is the plan for your query on your engine.

-- Postgres: actual timings, not estimates, plus buffer counts.
EXPLAIN (ANALYZE, BUFFERS) 
WITH recent AS (SELECT ...) SELECT ...;

-- MySQL
EXPLAIN ANALYZE WITH recent AS (SELECT ...) SELECT ...;

What to look for: a CTE Scan node in Postgres means it materialised. Its absence means it was inlined. If you see a sequential scan over a large table inside a CTE whose result you then filter to a handful of rows, you have found the fence.

The ANALYZE variant runs the query, so use it on a replica or with care in production. BUFFERS tells you how much was read from cache versus disk, which is often the actual explanation for a query that is fast in testing and slow in production.

When to reach for one

A short set of rules that hold across engines.

Use a CTE when it makes a complex query readable. This is the main reason and it is a good one. A query your colleague can follow is worth a small amount of performance.
Use a recursive CTE for hierarchies and graphs. There is no reasonable alternative in standard SQL.
Use one when the same intermediate result is needed several times, and materialise it explicitly so it is computed once.
Do not use one purely to avoid a subquery. The plan is usually identical and occasionally worse.
Do not put a CTE around a large table you then filter. That is the fence, and it is the expensive mistake.
Consider a view instead when the same expression is used across many queries, and a materialised view when it is expensive and does not need to be current.

One more, less about SQL: a query that needs five chained CTEs to express is often a signal about the schema rather than about the query. Denormalising, adding a summary table, or adding the index that makes the join cheap frequently beats a cleverer query.

The database introduction docs cover managed Postgres and MySQL on RunxBuild, both of which support everything above.

How this fits the rest of the stack

Query performance is usually a conversation about the plan and the indexes rather than about the plan size, but a database that is undersized turns every marginal query into a visible one. The RunxBuild hosting calculator shows the database ladder alongside the service, which makes it easier to tell a query problem from a capacity one before spending an afternoon rewriting SQL that was never the constraint.

Useful related references:

FAQ

What is a CTE in SQL?

A common table expression: a named temporary result set defined with a WITH clause and referenced by the statement that follows it. It exists only for the duration of that statement. Its main value is readability, letting a complex query be read top to bottom instead of inside out.

Are CTEs faster than subqueries?

Usually not. On most engines a simple CTE compiles to the same plan as the equivalent subquery. On some engines and versions it compiles to something worse, because materialisation prevents the planner from pushing predicates into it. Use them for clarity, and check the plan when performance matters.

What is a recursive CTE used for?

Walking hierarchies and graphs of unknown depth: organisation charts, category trees, bill-of-materials structures, threaded comments. It has an anchor query, a UNION ALL, and a term that joins back to the CTE itself. Nothing else in standard SQL expresses this.

What does MATERIALIZED mean in a CTE?

It forces the engine to compute the CTE fully and store the result rather than inlining it into the outer query. Useful when the CTE is expensive and referenced more than once. NOT MATERIALIZED forces inlining so the planner can optimise across the boundary.

Why is my CTE slower than the equivalent subquery?

Most likely an optimisation fence. If the engine materialises the CTE, it cannot push your outer WHERE clause into it, so a CTE selecting an entire table followed by a filter reads every row instead of using an index. Check the plan for a CTE Scan node and consider inlining.