SQL
Traverse Hierarchical Data Using Recursive CTEs
Master traversing parent-child relationships and tree-like structures in SQL databases using powerful `WITH RECURSIVE` Common Table Expressions for hierarchical data.
-- Example Table: categories (id, name, parent_id)
-- Sample data:
-- (1, 'Electronics', NULL)
-- (2, 'Phones', 1)
-- (3, 'Laptops', 1)
-- (4, 'Smartphones', 2)
-- (5, 'Feature Phones', 2)
-- Find all descendants of 'Electronics' (id = 1)
WITH RECURSIVE category_tree AS (
-- Anchor member: Select the starting node(s)
SELECT
id,
name,
parent_id,
1 as level,
CAST(name AS TEXT) as path
FROM
categories
WHERE
id = 1 -- Starting from 'Electronics'
UNION ALL
-- Recursive member: Join to find children
SELECT
c.id,
c.name,
c.parent_id,
ct.level + 1,
ct.path || ' -> ' || c.name
FROM
categories c
JOIN
category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
-- To find all ancestors, reverse the join condition (ct.parent_id = c.id) and start from a leaf node.
How it works: This snippet demonstrates how to traverse hierarchical data (like a category tree or organizational structure) using a `WITH RECURSIVE` Common Table Expression. It consists of two parts: an 'anchor member' that selects the starting nodes, and a 'recursive member' that joins back to the CTE itself to find related child nodes. The `UNION ALL` combines these results, effectively walking down the tree level by level until no more children are found, providing a complete list of descendants.