🌳 Self-Joins Are Painful. CTE Makes It Easy.
Org charts, categories, comments, folder trees — all hierarchical. Recursive CTEs query parent-child relationships elegantly.
📝 Employee Hierarchy
-- Table structure
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT REFERENCES employees(id)
);
-- Recursive CTE to get org tree
WITH RECURSIVE org_tree AS (
-- Anchor: Top-level managers
SELECT
id,
name,
manager_id,
0 AS level,
CAST(name AS VARCHAR(500)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: Employees under each manager
SELECT
e.id,
e.name,
e.manager_id,
ot.level + 1,
ot.path || ' → ' || e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
id,
REPEAT(' ', level) || name AS indented_name,
level,
path
FROM org_tree
ORDER BY path;
🎯 Category Tree with Breadcrumbs
WITH RECURSIVE category_tree AS (
SELECT
id,
name,
parent_id,
name AS breadcrumb
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT
c.id,
c.name,
c.parent_id,
ct.breadcrumb || ' > ' || c.name
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, breadcrumb
FROM category_tree
ORDER BY breadcrumb;
-- Output:
-- Electronics > Computers > Laptops
-- Electronics > Computers > Desktops
-- Electronics > Phones > Smartphones
-- Clothing > Men > Shirts
✅ Find All Subordinates
-- Get entire team under specific manager
WITH RECURSIVE team AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 123 -- Manager ID
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN team t ON e.manager_id = t.id
)
SELECT * FROM team
ORDER BY manager_id, name;
-- Count subordinates per manager
WITH RECURSIVE hierarchy AS (
SELECT id, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, h.depth + 1
FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
)
SELECT
m.name AS manager,
COUNT(e.id) AS subordinates,
MAX(e.depth) AS max_depth
FROM employees m
LEFT JOIN hierarchy e ON m.id = e.id
WHERE e.depth > 0
GROUP BY m.id, m.name;
💡 Performance Tips
- Add indexes on parent_id columns
- Limit recursion depth with WHERE clause
- Use materialized path for frequent queries
- Consider closure table for complex trees
- Set max_recursion_depth if needed
“Nested set model was too complex. Recursive CTE replaced 100 lines of code with 20 lines. Employee tree now loads in 50ms. Simple and maintainable.”
