snippetsqlModeratepending

SQL recursive CTE -- tree traversal and hierarchical data

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

recursive CTEWITH RECURSIVEtree traversalhierarchyparent_idself-join

postgresqlmysqlsqlite

Problem

Need to query hierarchical data (org charts, category trees, comment threads, file systems) stored in a parent_id self-referencing table. Regular JOINs cannot handle arbitrary depth.

Solution

Use recursive CTEs (WITH RECURSIVE) to traverse tree structures to any depth. Works in PostgreSQL, MySQL 8+, SQLite 3.8+.

Code Snippets

Recursive CTE for tree traversal

-- Get all descendants of a category
WITH RECURSIVE tree AS (
  -- Base case: start from root
  SELECT id, name, parent_id, 0 as depth,
         ARRAY[name] as path
  FROM categories WHERE id = 1

  UNION ALL

  -- Recursive case: join children
  SELECT c.id, c.name, c.parent_id, t.depth + 1,
         t.path || c.name
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 10  -- safety limit
)
SELECT id, name, depth,
       array_to_string(path, ' > ') as breadcrumb
FROM tree
ORDER BY path;

-- Result:
-- 1  Electronics  0  Electronics
-- 2  Phones       1  Electronics > Phones
-- 3  iPhones      2  Electronics > Phones > iPhones

Revisions (0)

No revisions yet.