Recursive CTE for Hierarchical Data with Depth, Path & Ancestor Tracking

🗃️ Code Snippet of the Day — July 4, 2026

Recursive CTE for Hierarchical Data with Depth, Path & Ancestor Tracking

A single SQL query that walks entire parent-child trees — org charts, category trees, comment threads, file systems, BOM — without self-joins or N+1 queries. Computes depth, full traversal path, all ancestors, and a visual tree view.

Why you need it:

• parent_id flat tables are everywhere but hard to query recursively


• Application-level recursion = N+1 query problem at scale


• This CTE gives you depth, paths, ancestors & a pretty tree in one shot

Quick usage:

WITH RECURSIVE hierarchy AS (
  SELECT id, name, parent_id, 0 AS depth, CAST(id AS TEXT) AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, h.depth + 1,
         h.path || ',' || CAST(c.id AS TEXT)
  FROM categories c JOIN hierarchy h ON c.parent_id = h.id
)
SELECT *, LPAD('  ', depth * 2) || '└─ ' || name AS tree_view
FROM hierarchy ORDER BY path;

💡 Works on PostgreSQL, MySQL 8+, SQLite 3.34+, SQL Server, Oracle — ANSI SQL:1999.