SQL CTE (Common Table Expressions) — recursive and non-recursive, with examples

A CTE is a named temporary result set defined with WITH ... AS (...) that exists only for the duration of one query. Two flavors: non-recursive (most common) and recursive (for hierarchies and graphs).

Non-recursive CTE — readability

WITH active_customers AS (
  SELECT id, name FROM customers WHERE last_login > NOW() - INTERVAL '30 days'
),
recent_orders AS (
  SELECT customer_id, SUM(total) AS spend
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT a.name, COALESCE(r.spend, 0) AS spend
FROM active_customers a
LEFT JOIN recent_orders r ON r.customer_id = a.id
ORDER BY spend DESC;

Same as a nested subquery, but reads top-to-bottom. Each CTE is named and can be referenced multiple times.

Multiple references — DRY queries

WITH top_products AS (
  SELECT product_id, SUM(qty) AS sold FROM order_items GROUP BY product_id
  ORDER BY sold DESC LIMIT 100
)
SELECT
  (SELECT COUNT(*) FROM top_products)         AS total,
  (SELECT AVG(sold) FROM top_products)        AS avg_sold,
  (SELECT MAX(sold) FROM top_products)        AS max_sold;

Recursive CTE — hierarchies (org chart, comments, categories)

-- Find every report (direct + indirect) of employee 5
WITH RECURSIVE reports AS (
  -- Anchor: direct reports
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id = 5

  UNION ALL

  -- Recursive: reports of reports
  SELECT e.id, e.name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports ORDER BY depth, name;

Recursive CTE — generate a date series

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

(In Postgres, prefer generate_series for date series — but the pattern shows the recursion mechanics.)

Recursive CTE — graph traversal (find all transitive permissions)

WITH RECURSIVE perms AS (
  SELECT role_id, permission_id FROM role_permissions WHERE role_id = 7
  UNION
  SELECT rp.role_id, rp.permission_id
  FROM role_permissions rp
  JOIN role_hierarchy rh ON rh.parent_role_id = rp.role_id
  JOIN perms p ON p.role_id = rh.child_role_id
)
SELECT DISTINCT permission_id FROM perms;

CTE vs subquery — Postgres-specific note

Until Postgres 12, CTEs were an "optimization fence" — the planner could not push predicates into them. Postgres 12+ inlines CTEs by default unless you mark them MATERIALIZED. So in modern Postgres, CTE vs equivalent subquery → same plan.

Production guidance

• Reach for CTEs to name subqueries and improve readability — top reason to use them.
• Recursive CTEs are the idiomatic way to walk trees/graphs in pure SQL.
• Don't expect a CTE to be cached across reuses unless you write MATERIALIZED (Postgres) — each reference may re-execute.