SQL Recursive CTEs: Querying Hierarchical and Tree Data
Hierarchical data is hard to query with plain SQL. Recursive CTEs solve this elegantly. Here is how I use them for trees, graphs, and hierarchies. Hierarchical data is everywhere. Organization charts, category trees, threaded comments, bill-of-materials assemblies, file systems. All are trees where each row references a parent. Querying these structures with plain SQL is painful because you do not know the depth in advance. You cannot write enough JOINs to traverse an arbitrary tree. Recursive CTEs solve this by letting a query reference itself, traversing the tree to any depth in a single statement. The Structure of a Recursive CTE A recursive CTE has two parts: a base case and a recursive case. The base case selects the starting rows, typically the root of the tree. The recursive case joins the previous result to the table, finding the children of the rows found so far. The database repeats the recursive step until no new rows are found. WITH RECURSIVE category_tree AS ( . Base case: root categories (no parent) SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL UNION ALL . Recursive case: children of the previous level SELECT c.id,…