Loading Depth Study
Preparing your workspace
CTEs create named intermediate query results, while recursive CTEs can repeatedly process parent-child relationships.
Common Table Expressions use the WITH clause to define temporary named query results that can make complex SQL easier to read and maintain. PostgreSQL also supports recursive CTEs for hierarchical structures such as organization trees, category trees, and dependency graphs. Recursive queries have an anchor part and a recursive part. They should be designed carefully to avoid unnecessary work or infinite recursion.
An organization management system can use a recursive CTE to find all employees beneath a manager in a hierarchy.
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e
INNER JOIN org_tree o
ON e.manager_id = o.id
)
SELECT *
FROM org_tree
ORDER BY depth, name;The recursive query walks through an employee hierarchy and calculates the depth of each employee.