CTEs — WITH clause, recursive CTEs
Concept
CTEs (Common Table Expressions) are named temporary result sets defined with the WITH clause. They make complex queries more readable by breaking them into named steps.
Syntax: WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name.
Multiple CTEs: WITH cte1 AS (...), cte2 AS (...) SELECT .... CTEs can reference earlier CTEs.
Why use CTEs over subqueries:
- Readable — name your intermediate result sets.
- Avoid repetition — reference the CTE multiple times without duplicating the subquery.
- Testable — build up step by step.
Materialization: In some databases, CTEs are materialized (computed once, stored). In others, they're "inlined" (replaced by the optimizer). PostgreSQL 12+ materializes by default when referenced more than once. MySQL 8.0 inlines by default. Use WITH cte AS MATERIALIZED (...) or NOT MATERIALIZED in PostgreSQL.
Recursive CTEs: A CTE that references itself. Used for hierarchical data (trees, graphs): org charts, category trees, file systems, bill of materials.
Recursive CTE structure:
WITH RECURSIVE cte AS (
SELECT ... -- anchor member (base case)
UNION ALL
SELECT ... FROM cte -- recursive member (references self)
)
SELECT * FROM cte;Termination: The recursive part must have a condition that eventually produces 0 rows. Or use MAXRECURSION/LIMIT to prevent infinite loops.
Code Example
-- Simple CTE — cleaner than nested subqueries
WITH top_customers AS (
SELECT user_id, SUM(total) AS lifetime_value
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING SUM(total) > 1000
)
SELECT u.name, u.email, tc.lifetime_value
FROM users u
JOIN top_customers tc ON tc.user_id = u.id
ORDER BY tc.lifetime_value DESC;
-- Multiple CTEs — chained steps
WITH monthly AS (
SELECT
DATE_FORMAT(created_at, '%Y-%m') AS month,
COUNT(*) AS order_count,
SUM(total) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
),
monthly_with_growth AS (
SELECT
month, order_count, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly
)
SELECT
month,
revenue,
ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
FROM monthly_with_growth
ORDER BY month;
-- Recursive CTE — category tree (parent-child hierarchy)
-- categories: id, name, parent_id
WITH RECURSIVE category_tree AS (
-- Anchor: start with root categories (no parent)
SELECT id, name, parent_id, 0 AS depth, CAST(name AS CHAR(500)) AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Recursive: join children to current level
SELECT c.id, c.name, c.parent_id, ct.depth + 1, CONCAT(ct.path, ' > ', c.name)
FROM categories c
JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, depth, path
FROM category_tree
ORDER BY path;
-- Result:
-- id | name | depth | path
-- 1 | Electronics | 0 | Electronics
-- 3 | Phones | 1 | Electronics > Phones
-- 7 | Smartphones | 2 | Electronics > Phones > Smartphones
-- 2 | Clothing | 0 | Clothing
-- Find an employee's full management chain (bottom-up)
WITH RECURSIVE management_chain AS (
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE id = 42 -- start from employee 42
UNION ALL
SELECT e.id, e.name, e.manager_id, mc.level + 1
FROM employees e
JOIN management_chain mc ON e.id = mc.manager_id
)
SELECT level, name FROM management_chain ORDER BY level;