SQL CTEs & Subqueries: WITH, Recursive CTEs & Correlated Queries

Q: Can I use a CTE in UPDATE or DELETE?

Yes. WITH ids AS (SELECT …) UPDATE t SET … WHERE id IN (SELECT id FROM ids) works in most databases, and PostgreSQL also allows data-modifying statements inside CTEs themselves.

SQL CTEs & Subqueries

Real-world SQL rarely fits in a single SELECT … FROM … WHERE. You filter, aggregate, rank, join the result to something else, and aggregate again. Subqueries nest one query inside another. Common table expressions (CTEs), introduced with WITH, name those intermediate steps so a complex query reads top to bottom like a pipeline. Recursive CTEs go further, traversing hierarchies and graphs such as org charts, category trees, bills of materials, and dependency chains.

Knowing when to use each, and how databases execute them, turns unreadable 200-line queries into maintainable ones, and avoids performance surprises from correlated subqueries or CTE materialization.

TL;DR

A CTE (WITH name AS (SELECT …)) defines a named, temporary result used by the main query. Chain several for step-by-step logic.
Recursive CTEs (WITH RECURSIVE) repeatedly join a working set to itself, which is how you walk trees and graphs.
Subqueries can be scalar (one value), row or list (IN), table (derived tables in FROM), or existence checks (EXISTS).
Correlated subqueries reference the outer row and conceptually run per row. They're fine for EXISTS, and risky for large per-row computations.
Modern planners usually inline CTEs; use MATERIALIZED / NOT MATERIALIZED (PostgreSQL) to control it when needed.
Prefer CTEs for readability, EXISTS for filtering, and window functions over correlated subqueries for per-row analytics.

Quick Example

A multi-step revenue report as a readable CTE pipeline:

And a recursive CTE walking a category tree:

Core Concepts

Common Table Expressions

Each CTE can reference earlier ones, which builds a pipeline.
A CTE can be referenced several times in the main query.
CTEs exist only for that statement; for reuse across queries, use views or temporary tables.
Column names can be declared: WITH totals(customer_id, revenue) AS (…).

Recursive CTEs

A recursive CTE has an anchor member (the starting rows) and a recursive member that references the CTE itself, combined with UNION ALL (or UNION, which deduplicates). The database repeatedly runs the recursive part on the rows produced by the previous iteration until no new rows appear.

Uses:

Hierarchies: org charts, comment threads, category trees, and file systems (see trees).
Graphs: reachability, shortest hops, dependency resolution (with cycle detection).
Series generation: dates or numbers (though generate_series is simpler in PostgreSQL).

Always guard against cycles (track visited IDs in a path array, or use PostgreSQL 14+'s CYCLE clause) and consider a depth limit, or cyclic data will loop until resources run out.

Kinds of Subqueries

Correlated Subqueries

A correlated subquery references columns from the outer query:

Conceptually it runs once per outer row. Planners often transform EXISTS and IN forms into efficient semi joins, but scalar correlated subqueries in SELECT may truly execute per row. That's fine with an index on orders(customer_id, placed_at) for modest row counts, and slow for millions. Alternatives are joining to a pre-aggregated CTE, or window functions.

Materialization

Historically, some databases (PostgreSQL before 12) always materialized CTEs, computing them once as an "optimization fence" that blocked predicate pushdown and could be much slower. Modern PostgreSQL inlines non-recursive CTEs referenced once, and you can control it:

Other engines differ: SQL Server inlines CTEs (they're like inline views), and some warehouses cache CTE results. Check EXPLAIN when performance matters.

Data-Modifying CTEs

PostgreSQL allows INSERT, UPDATE, and DELETE with RETURNING inside WITH, which makes multi-step changes atomic in one statement:

Best Practices

Name Steps After What They Contain

paid_orders, customer_revenue, churned_customers read like documentation. Avoid t1, cte2, and temp. Well-named CTEs make complex analytics queries reviewable.

Filter Early

Apply the most selective filters in the earliest CTE or subquery, so later steps process fewer rows, and so predicates can use indexes.

Use EXISTS Rather Than IN for Correlated Checks

EXISTS stops at the first match, handles NULLs predictably, and planners optimize it well. NOT IN with a nullable subquery is a known correctness trap. See NULL handling.

Consider Temporary Tables for Very Complex Pipelines

When an intermediate result is large, reused across several queries, or needs its own index, a temporary table (or a staging table in a warehouse, or a model in dbt) can beat a giant single statement.

Common Mistakes

Scalar Subquery Returning Multiple Rows

Constrain it (ORDER BY … LIMIT 1), aggregate it, or restructure it as a join.

Recursive CTE Without a Cycle Guard

A single bad row where parent_id points to a descendant turns a tree into a cycle, and the query runs until it hits memory or time limits. Track visited nodes, or use the CYCLE clause.

Assuming a CTE Is Computed Once

On engines that inline CTEs, a CTE referenced three times may be computed three times. If it's expensive, materialize it explicitly or use a temporary table.

FAQ

What's the difference between a CTE and a subquery?

Functionally, a non-recursive CTE is a named subquery. CTEs improve readability (logic flows top to bottom), can be referenced multiple times, and support recursion. Performance is usually the same in modern databases, since both are typically inlined and optimized together.

When should I use a recursive CTE?

Whenever you need to traverse relationships of unknown depth: all descendants of a category, the management chain above an employee, all dependencies of a package, or paths in a graph. For very large or frequently queried hierarchies, also consider materialized paths, nested sets, PostgreSQL's ltree, or a graph database.

Are correlated subqueries always slow?

No. With an index on the correlated column, and when the planner can convert them to joins (common for EXISTS and IN), they're efficient. Scalar correlated subqueries over large tables can be slow; rewrite them with joins to aggregated CTEs or window functions if EXPLAIN shows per-row execution.

Can I use a CTE in UPDATE or DELETE?

Yes. WITH ids AS (SELECT …) UPDATE t SET … WHERE id IN (SELECT id FROM ids) works in most databases, and PostgreSQL also allows data-modifying statements inside CTEs themselves.