SQL
Querying Hierarchical Data Using Recursive CTEs
Explore how to navigate and query tree-like or hierarchical data structures in SQL, such as nested categories or comments, using powerful Recursive Common Table Expressions.
WITH RECURSIVE CategoryTree AS (
-- Anchor member: Select the top-level categories
SELECT id, name, parent_id, 0 AS level
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Recursive member: Join to find children
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c
JOIN CategoryTree ct ON c.parent_id = ct.id
)
SELECT id, name, parent_id, level
FROM CategoryTree
ORDER BY level, id;
How it works: This snippet uses a Recursive Common Table Expression (CTE) to traverse and retrieve hierarchical data, such as a category tree. The `Anchor Member` selects the initial top-level items (where `parent_id` is NULL). The `Recursive Member` then repeatedly joins back to the CTE itself to find children of the previously found items, incrementing the `level`. This process continues until no more children are found, allowing you to flatten and query the entire hierarchy.