Recursive CTEs
Recursive CTEs query hierarchical and graph data - categories with subcategories, organizational charts, bill of materials, file system trees, and graph traversal. They use a base case plus a recursive step that references the CTE itself.
Recursive CTE Structure
- WITH RECURSIVE name AS (base_query UNION ALL recursive_query)
- Base query - starting point (root nodes, initial values)
- Recursive query - references the CTE to get the next level
- Terminates when the recursive step returns 0 rows
- depth column helps track nesting level and prevent infinite loops
- Add WHERE depth < 10 as a safety limit
- Useful for: org charts, category trees, file paths, number sequences
Recursive CTE Examples
-- Generate a sequence of numbers 1-10
WITH RECURSIVE nums AS (
SELECT 1 AS n -- base case
UNION ALL
SELECT n + 1 FROM nums -- recursive step
WHERE n < 10 -- termination condition
)
SELECT n FROM nums;
-- Result: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
-- Employee hierarchy (hypothetical table)
-- employees: id, name, manager_id
WITH RECURSIVE hierarchy AS (
-- Base: root employees (no manager)
SELECT id, name, manager_id, 0 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
-- Recursive: find direct reports
SELECT e.id, e.name, e.manager_id, h.depth + 1
FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
WHERE h.depth < 10 -- prevent infinite loop
)
SELECT depth, name FROM hierarchy ORDER BY depth, name;
-- Generate a date series (one row per day, last 7 days)
WITH RECURSIVE date_series AS (
SELECT CURRENT_DATE AS d
UNION ALL
SELECT d - 1 FROM date_series WHERE d > CURRENT_DATE - 7
)
SELECT d FROM date_series ORDER BY d;
-- Revenue by day (including 0-revenue days using date_series)
WITH RECURSIVE dates AS (
SELECT CURRENT_DATE - 6 AS d
UNION ALL
SELECT d + 1 FROM dates WHERE d < CURRENT_DATE
)
SELECT d.d, COALESCE(SUM(p.amount), 0) AS revenue
FROM dates d
LEFT JOIN payments p ON DATE(p.paid_at) = d.d
GROUP BY d.d ORDER BY d.d;Quick Quiz
Tip
Tip
Practice Recursive CTEs in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Hierarchical queries. Set max recursion.
Common Mistake
Warning
A common mistake with Recursive CTEs is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Recursive CTEs from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Recursive CTEs query hierarchical and graph data - categories with subcategories, organizational charts, bill of materials, file system trees, and graph traversal.
- WITH RECURSIVE name AS (base_query UNION ALL recursive_query)
- Base query - starting point (root nodes, initial values)
- Recursive query - references the CTE to get the next level