SQL
Query Hierarchical Data with SQL Recursive Common Table Expressions
Navigate and query hierarchical or tree-like data structures efficiently using SQL Recursive Common Table Expressions (RCTE) for categories, comments, or org charts.
WITH RECURSIVE CategoryPath AS (
-- Anchor member: Select the starting category (e.g., parent_id is NULL or a specific ID)
SELECT
id,
name,
parent_id,
1 AS level,
CAST(name AS TEXT) AS path
FROM
categories
WHERE
parent_id IS NULL -- Or WHERE id = [starting_category_id]
UNION ALL
-- Recursive member: Join to find children
SELECT
c.id,
c.name,
c.parent_id,
cp.level + 1 AS level,
cp.path || ' -> ' || c.name AS path
FROM
categories c
JOIN
CategoryPath cp ON c.parent_id = cp.id
)
SELECT
id,
name,
parent_id,
level,
path
FROM
CategoryPath
ORDER BY
path;
How it works: This snippet demonstrates how to use a Recursive Common Table Expression (RCTE) to query hierarchical data, such as categories, comments, or organizational structures. An RCTE consists of an 'anchor member' which is the base query that starts the recursion, and a 'recursive member' that references the CTE itself to find subsequent rows (children) until no more rows are found. In this example, it builds a complete path from root to leaf for each category, showing its `level` in the hierarchy. This is incredibly useful for displaying tree structures or performing operations on entire branches of hierarchical data.