What is a Recursive Query?
A query that calls itself - like a loop.
Simple analogy: Like climbing stairs - go up one step, then repeat until you reach the top.
Common Use Case: Organizational Hierarchy
Employee table where each employee has a manager:
| id | name | manager_id | |----|-------|------------| | 1 | CEO | NULL | | 2 | VP | 1 | | 3 | Manager | 2 | | 4 | Employee | 3 |
Recursive CTE Example
Find all employees under CEO:
WITH RECURSIVE employee_tree AS (
-- Start: Get CEO
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: Get employees under each person
SELECT e.id, e.name, e.manager_id, et.level + 1
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree;How it works:
- Start with CEO (level 1)
- Find employees reporting to CEO (level 2)
- Find employees reporting to them (level 3)
- Repeat until no more employees
Another Example: Category Tree
WITH RECURSIVE category_tree AS (
-- Get top-level categories
SELECT id, name, parent_id, 1 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Get child categories
SELECT c.id, c.name, c.parent_id, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;Summary
Recursive = Query calls itself Use for:
- Organizational charts
- Category trees
- File system hierarchies
- Any parent-child relationships