WITH name AS (subquery) defines a common table expression: a named, temporary result set that the rest of the query can reference as if it were a real table, improving readability over deeply nested subqueries. A recursive CTE (WITH RECURSIVE) references itself, repeatedly executing until it produces no new rows — the standard SQL technique for hierarchical data like an org chart, category tree, or graph traversal where the depth isn't known ahead of time.
A basic CTE: naming a subquery for clarity
Compared to nesting the same subquery directly in FROM, naming it with WITH makes multi-step queries dramatically easier to read top to bottom.
WITH high_value_orders AS (
SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000
)
SELECT customers.name, high_value_orders.total_spent
FROM customers
JOIN high_value_orders ON customers.id = high_value_orders.customer_id;Multiple CTEs in one query
Separate WITH clauses can each name a different intermediate result, and later CTEs can reference earlier ones — building up a multi-step calculation in clearly labeled stages.
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
FROM orders
GROUP BY month
),
best_month AS (
SELECT month FROM monthly_sales ORDER BY revenue DESC LIMIT 1
)
SELECT * FROM monthly_sales WHERE month = (SELECT month FROM best_month);Recursive CTEs: walking a hierarchy
A recursive CTE has two parts joined by UNION ALL: a base case (the starting rows) and a recursive part that references the CTE's own name, repeating until no new rows are produced.
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL -- base case: the top of the org chart
UNION ALL
SELECT e.id, e.name, e.manager_id, org_chart.level + 1
FROM employees e
JOIN org_chart ON e.manager_id = org_chart.id -- recursive part
)
SELECT * FROM org_chart ORDER BY level;