SQL
Query Hierarchical Data with Recursive Common Table Expressions
Discover how to traverse and query tree-like or hierarchical data structures (e.g., organizational charts, categories) using powerful SQL Recursive CTEs.
WITH RECURSIVE CategoryPath AS (
SELECT
category_id,
category_name,
parent_id,
CAST(category_name AS TEXT) as full_path,
1 as level
FROM
categories
WHERE
parent_id IS NULL OR parent_id = 0 -- Top-level categories
UNION ALL
SELECT
c.category_id,
c.category_name,
c.parent_id,
cp.full_path || ' -> ' || c.category_name,
cp.level + 1
FROM
categories c
JOIN
CategoryPath cp ON c.parent_id = cp.category_id
)
SELECT
category_id,
category_name,
parent_id,
full_path,
level
FROM
CategoryPath
ORDER BY
full_path;
How it works: This SQL snippet uses a Recursive Common Table Expression (CTE) named `CategoryPath` to traverse and display hierarchical data, such as product categories. The `ANCHOR` part selects the top-level categories. The `RECURSIVE` part then iteratively joins back to itself to build the `full_path` and `level` for all child categories, effectively flattening the hierarchy for easier querying.