SQL CTE Tutorial — WITH Clause + Recursive Queries with Examples

Basic CTE syntax

WITH high_value_users AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '30 days'
  GROUP BY user_id
  HAVING SUM(amount) > 1000
)
SELECT u.email, hvu.total
FROM high_value_users hvu
JOIN users u ON u.id = hvu.user_id
ORDER BY hvu.total DESC;

Read the query top-to-bottom, then top-to-bottom again — that's the biggest readability win over deeply-nested subqueries.

CTE vs subquery vs temp table — pick the right one

Situation	Best choice
Used once, one query	Subquery (or CTE for readability)
Referenced 2+ times in same query	CTE (avoids duplication)
Referenced across multiple queries	Temp table (persists in session)
Very large intermediate result	Temp table with an index
Hierarchy or graph traversal	Recursive CTE (only option in standard SQL)

Multiple CTEs — chain them

WITH
recent_orders AS (
  SELECT * FROM orders WHERE created_at >= CURRENT_DATE - 30
),
per_user AS (
  SELECT user_id, COUNT(*) AS n, SUM(amount) AS total
  FROM recent_orders
  GROUP BY user_id
)
SELECT * FROM per_user WHERE n > 3 ORDER BY total DESC;

Recursive CTE — the killer feature

A recursive CTE has two parts joined by UNION ALL: the anchor (base case) and the recursive step (references the CTE itself). The engine keeps executing the step until it produces zero new rows.

Walk an org chart from a specific employee to the CEO

WITH RECURSIVE chain_of_command AS (
  -- anchor: the starting employee
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE id = 42

  UNION ALL

  -- recursive step: their manager, then that manager's manager, ...
  SELECT e.id, e.name, e.manager_id, coc.depth + 1
  FROM employees e
  JOIN chain_of_command coc ON e.id = coc.manager_id
)
SELECT * FROM chain_of_command ORDER BY depth;

All descendants of a folder

WITH RECURSIVE descendants AS (
  SELECT id, name, parent_id FROM folders WHERE id = :root_id
  UNION ALL
  SELECT f.id, f.name, f.parent_id
  FROM folders f JOIN descendants d ON f.parent_id = d.id
)
SELECT * FROM descendants;

Generate a series of dates

WITH RECURSIVE dates AS (
  SELECT DATE '2026-01-01' AS d
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM dates WHERE d < DATE '2026-12-31'
)
SELECT d FROM dates;

Postgres has generate_series() for this, but the recursive form is portable to any SQL that supports CTEs.

Materialization — the perf gotcha

Postgres 11 and earlier always materialized CTEs (executed once, stored, then read from). This was an "optimization fence" — sometimes helpful, sometimes catastrophic. Postgres 12+ inlines CTEs by default when possible; use WITH ... AS MATERIALIZED (...) to force old behavior.

Other databases: SQL Server, Oracle, and MySQL 8+ inline by default. SQLite and DuckDB inline as well. If you're on a legacy Postgres, CTEs can silently make queries slower.

Infinite recursion — the CTE gotcha

If your recursive step doesn't terminate, the query runs forever (or until temp space fills up). Always add a depth cap for graph traversal:

WITH RECURSIVE walk AS (
  SELECT id, 0 AS depth FROM nodes WHERE id = :start
  UNION ALL
  SELECT e.dst, w.depth + 1
  FROM edges e JOIN walk w ON e.src = w.id
  WHERE w.depth < 10  -- HARD CAP
)
SELECT DISTINCT id FROM walk;

Writable CTEs

Postgres and SQL Server let you use INSERT, UPDATE, and DELETE inside a CTE — the RETURNING clause makes the affected rows available to subsequent CTEs:

WITH deleted AS (
  DELETE FROM orders WHERE created_at < NOW() - INTERVAL '2 years'
  RETURNING id, user_id
)
INSERT INTO archived_orders SELECT * FROM deleted;

Related

SQL Window Functions Cheat Sheet · SQL Formatter to prettify your CTEs.