SQL
Querying Hierarchical Data Using Recursive Common Table Expressions
Learn to navigate and query tree-like or hierarchical data structures in SQL using powerful recursive Common Table Expressions (CTEs), ideal for categories, comments, or organizational charts.
-- Assuming a 'categories' table with 'id', 'name', 'parent_id'
WITH RECURSIVE category_path AS (
-- Anchor member: Select all root categories (those without a parent)
SELECT
id,
name,
parent_id,
1 AS level,
CAST(name AS VARCHAR(255)) AS path
FROM
categories
WHERE
parent_id IS NULL
UNION ALL
-- Recursive member: Join to find children
SELECT
c.id,
c.name,
c.parent_id,
cp.level + 1 AS level,
CAST(cp.path || ' -> ' || c.name AS VARCHAR(255)) AS path
FROM
categories c
INNER JOIN
category_path cp ON c.parent_id = cp.id
)
SELECT
id,
name,
parent_id,
level,
path
FROM
category_path
ORDER BY
path;
How it works: This snippet demonstrates how to query hierarchical data (like categories with parent-child relationships) using a Recursive Common Table Expression (CTE). The CTE `category_path` has two parts: an "anchor member" that selects the root nodes (where `parent_id` is NULL), and a "recursive member" that repeatedly joins with itself to find child nodes, incrementing the `level` and building a `path` string. This allows for powerful traversal and display of tree-like data structures in SQL, providing clear insight into relationships.