SQL CTE Tutorial: Write Readable Queries with WITH Clauses

Overview

A Common Table Expression (CTE) is a named temporary result set you define with the WITH keyword. CTEs turn deeply nested subqueries into readable, step-by-step logic. They also enable recursive queries, which are the standard way to walk hierarchical data in SQL.

Basic Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT *
FROM cte_name;

A CTE exists only for the duration of the query that follows. It is not stored or reused across statements.

Rewriting a Nested Subquery

Subquery version:

SELECT region, avg_amount
FROM (
  SELECT region, AVG(amount) AS avg_amount
  FROM sales
  GROUP BY region
) AS regional
WHERE avg_amount > 200;

CTE version:

WITH regional AS (
  SELECT region, AVG(amount) AS avg_amount
  FROM sales
  GROUP BY region
)
SELECT region, avg_amount
FROM regional
WHERE avg_amount > 200;

Both produce the same result. The CTE version reads top-down and names each step, which matters more as the logic grows.

Chaining Multiple CTEs

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount) AS total
  FROM sales
  GROUP BY 1
),
with_growth AS (
  SELECT
    month,
    total,
    LAG(total) OVER (ORDER BY month) AS prev_total
  FROM monthly
)
SELECT
  month,
  total,
  ROUND(100.0 * (total - prev_total) / prev_total, 2) AS growth_pct
FROM with_growth
WHERE prev_total IS NOT NULL
ORDER BY month;

Each CTE builds on the previous one. This is far easier to debug than a stack of nested subqueries because you can run each CTE in isolation.

CTE vs Subquery vs Temporary Table

Referencing a CTE Multiple Times

WITH active_users AS (
  SELECT id, name, last_login
  FROM users
  WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT
  (SELECT COUNT(*) FROM active_users) AS active_count,
  (SELECT AVG(EXTRACT(DAY FROM NOW() - last_login)) FROM active_users) AS avg_days_since_login;

Some databases materialize a CTE that is referenced more than once, which can improve performance. Others re-evaluate it each time. Check EXPLAIN output for your engine.

Recursive CTEs

A recursive CTE has two parts joined by UNION ALL: an anchor query and a recursive query that references the CTE itself.

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level employees
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: employees reporting to the previous level
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

This walks an org hierarchy from the CEO down. The recursion stops when the recursive query returns no rows.

Recursive Example: Date Series

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

This generates every day in January 2026. Useful for filling gaps in reporting queries.

Some engines use different syntax for generating series:

Using a CTE with INSERT, UPDATE, DELETE

WITH expired AS (
  SELECT id FROM sessions WHERE expires_at < NOW()
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM expired);

WITH sales_summary AS (
  SELECT region, SUM(amount) AS total
  FROM sales
  WHERE sale_date >= CURRENT_DATE - 30
  GROUP BY region
)
UPDATE regions r
SET last_month_total = s.total
FROM sales_summary s
WHERE r.name = s.region;

CTEs attached to a DML statement in PostgreSQL and SQL Server are evaluated as part of the same transaction.

Performance Notes

• In PostgreSQL 12+, CTEs are inlined by default unless they contain a VOLATILE function. Use WITH ... AS MATERIALIZED to force materialization.
• In older PostgreSQL versions, every CTE was an optimization fence. The rewrite often hurt performance.
• In MySQL 8, CTEs are materialized when referenced more than once.
• Always run EXPLAIN before and after rewriting a subquery as a CTE.
• For very large intermediate results, a temp table may be faster than a CTE because you can index it.

Readability Guidelines