What is a Recursive Query?
Employee Hierarchy Example
| id | name | manager_id | level |
|---|---|---|---|
| 1 | CEO | NULL | 1 |
| 2 | VP | 1 | 2 |
| 3 | Manager | 2 | 3 |
| 4 | Employee | 3 | 4 |
4 rows
A query that calls itself repeatedly until done.
Example: Org Chart
Employee Hierarchy Example
| id | name | manager_id | level |
|---|---|---|---|
| 1 | CEO | NULL | 1 |
| 2 | VP | 1 | 2 |
| 3 | Manager | 2 | 3 |
| 4 | Employee | 3 | 4 |
4 rows
WITH RECURSIVE org AS (
-- Start: Get top person
SELECT id, name, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
-- Repeat: Get their reports
SELECT e.id, e.name, o.level + 1
FROM employees e
JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;How it works:
- Get CEO (level 1)
- Find people reporting to CEO (level 2)
- Find people reporting to them (level 3)
- Repeat until no more
Use Cases
Employee Hierarchy Example
| id | name | manager_id | level |
|---|---|---|---|
| 1 | CEO | NULL | 1 |
| 2 | VP | 1 | 2 |
| 3 | Manager | 2 | 3 |
| 4 | Employee | 3 | 4 |
4 rows
- Organization hierarchies
- Category trees (Electronics > Phones > iPhone)
- File/folder structures
- Any parent-child data
Summary
Employee Hierarchy Example
| id | name | manager_id | level |
|---|---|---|---|
| 1 | CEO | NULL | 1 |
| 2 | VP | 1 | 2 |
| 3 | Manager | 2 | 3 |
| 4 | Employee | 3 | 4 |
4 rows
- WITH RECURSIVE: Query calls itself
- First part: Starting point
- UNION ALL: Combine results
- Second part: Find next level