Recursive Queries and Common Table Expressions

What CTEs Give You

A common table expression (the WITH clause) names a subquery so you can reuse it and read queries top-down instead of inside-out.

Simple CTE

WITH recent AS (
  SELECT * FROM orders WHERE created_at > now() - interval '30 days'
)
SELECT customer_id, COUNT(*) FROM recent GROUP BY customer_id;

Recursive CTE

WITH RECURSIVE org AS (
  SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

Generating a Number Series

WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

Key Points

• Use WITH to make complex queries readable.
• RECURSIVE repeats the UNION ALL branch until no new rows appear.
• Recursion handles trees like org charts, menus and comment threads.
• Always cap recursion depth to avoid runaway loops.