Hierarchy Queries

Question: How do you query hierarchy data in SQL, like an org chart, folder tree, or category tree?

Answer: The most common solution is a recursive CTE, which is a common table expression that can refer to itself. You start with the root rows in the anchor query, then repeatedly join each parent row to its children in the recursive part until no new rows are found. For a fixed depth, self-joins can work, but for real hierarchies, recursive CTEs are cleaner and scale to any depth.

Interview-Ready Answer: I would use a recursive CTE. I begin with the root row or rows, then recursively join the table back to the growing result set to pull each next level, usually adding a depth column and sometimes a path column for ordering. That gives me a readable query for descendants, ancestors, and breadcrumbs, and I would also mention indexing the parent key and guarding against cycles or recursion limits.

What a hierarchy query really means

Most hierarchy tables use an adjacency list, which is a table where each row points to its parent row with something like parent_id. That is simple to write and easy to insert into, but SQL cannot know the full tree depth from a single join. A hierarchy query is the process of walking that parent-child chain until you reach the root or the leaves.

How the recursive CTE works under the hood

1. Anchor member: the first SELECT finds the starting rows, usually the roots where parent_id IS NULL or a specific node like a chosen manager.
2. Working table: the database stores the current result set in an internal temporary structure. A working table is just a temporary result set the engine uses while it keeps expanding the tree.
3. Recursive member: the second SELECT joins the base table to the rows found so far. Each pass discovers the next layer of children or parents.
4. Repeat: the engine keeps looping until the recursive member returns no new rows, or until an engine limit is hit.
5. Final projection: once the loop stops, you can sort by depth, path, or a custom key to show the tree in a useful order.

This is why the pattern feels like a controlled flood: one row becomes many, those many become more rows, and the process stops only when there is nothing left to add.

When to use it

• Use recursive CTEs when depth is unknown or changes over time.
• Use self-joins only when the tree is guaranteed to be shallow and fixed, like exactly 2 or 3 levels.
• Use a closure table when reads are very frequent and you can afford extra storage and write complexity. A closure table stores every ancestor-descendant pair up front, so reads are fast but updates are more expensive.

Comparison of common approaches

Performance and complexity

For a true tree with n reachable rows, a recursive CTE is usually O(n) in row visits if the parent key is indexed, because each row is discovered once. If parent_id is not indexed, the engine may rescan the table at each level, which can turn into something much closer to O(n^2) work on large tables. Memory usage is tied to the breadth of the tree, because the engine has to keep the current frontier of rows while it expands the next level.

• Index the parent column: a B-tree on parent_id is the usual win.
• Use UNION ALL unless you truly need deduplication: UNION adds duplicate removal work and can hide legitimate repeated paths in a graph-like structure.
• Watch engine limits: SQL Server defaults to MAXRECURSION 100 unless you override it; MySQL 8 uses cte_max_recursion_depth with a default of 1000. PostgreSQL has no small fixed default cap, but bad data can still make the query run until it is stopped or until no new rows appear.

Important edge cases

• Cycles: if row A points to B and B points back to A, the query can loop forever unless the engine has a recursion cap or you add cycle checks. A cycle is a loop in the graph where you can revisit the same node.
• Orphans: a row with a parent that does not exist will not appear under any root. That is often a data-quality bug, not a query bug.
• Duplicate paths: in a directed acyclic graph, one node can be reached by more than one route, so you may see multiple rows for the same node unless you intentionally collapse them.
• Order is not automatic: the database does not promise a pretty tree order just because the query is recursive. If you want breadcrumb-style output, build a sort key or path column and order by it in the outer query.

Memory hook: Think of the anchor row as the front door of a building and the recursive step as walking room by room with a flashlight until you find no more doors. Once the flashlight finds nothing new, the search stops.

Real-world story

Imagine an e-commerce catalog service that stores categories like Electronics → Computers → Laptops. The frontend needs the full list of subcategories for the left navigation, and the breadcrumb bar needs the full path back to the root. A recursive CTE lets the service fetch all descendants of Electronics in one query and build a clean breadcrumb like Electronics > Computers > Laptops.

What goes wrong when people misunderstand hierarchy queries? A bad import can create an orphan category or a cycle. The symptom is usually a slow page, a timeout, or a sudden database error like a recursion-limit failure. Users see missing menu items, the search filter becomes inconsistent, and logs show repeated scans of the same category IDs or a warning that the maximum recursion depth was reached.

That is why hierarchy queries matter in production: they are not just about SQL syntax, they protect navigation, permissions, reporting, and anything else that depends on the tree being complete and ordered.

Follow-up & Tricky Questions

• How do you get all ancestors of a node instead of all descendants? Start from the chosen node in the anchor member, then recursively join on parent_id in the opposite direction. The pattern is the same; only the join direction changes.
• How do you build breadcrumbs? Carry a path column through the recursion, concatenating each name as you go. The final query can then order by the path or display it directly.
• How do you stop bad data from creating an infinite loop? Add a visited-path check, keep a depth limit, or rely on an engine cap such as SQL Server's MAXRECURSION. In production, I usually want both a data fix and a query safeguard.
• When would you choose a closure table instead of a recursive CTE? When read performance matters more than write simplicity, such as a permission tree or a very large product catalog that is queried constantly. Closure tables trade extra storage and maintenance for very fast ancestor and descendant lookups.
• How do you get only leaf nodes? After building the hierarchy, filter rows that have no children, usually by checking that no row exists with their ID as parent_id. A leaf is a node with zero children.
• Does ORDER BY inside the recursive member guarantee tree order? No. The only reliable order is the one you produce in the outer query, usually with a path, depth, or sort key.
• Is UNION better than UNION ALL here? Usually no. UNION ALL is faster and preserves every path, while UNION removes duplicates and can add overhead or hide important graph structure.
• Can a recursive CTE return the same node more than once? Yes, especially in a directed acyclic graph where a node can have more than one parent path. That is normal unless you intentionally deduplicate or model the data as a strict tree.
• What happens if the hierarchy is deeper than the engine limit? The query stops with an error or returns incomplete results, depending on the database. That is why interviewers like to hear both the SQL pattern and the safety plan.

Common Mistakes

• Using self-joins for unknown depth: that works only when you know the tree is tiny and fixed. Correction: use a recursive CTE when the depth can change.
• Forgetting the root filter: if the anchor member is wrong, you either miss part of the tree or start from the wrong node. Correction: be explicit about the starting condition.
• Ignoring indexes: without an index on parent_id, recursion can degrade into repeated scans. Correction: index the parent key and test with realistic data volumes.
• Assuming the output is naturally sorted: recursion does not promise pretty hierarchy order. Correction: build and sort by a path or ordering column.

Memory Hook

The anchor is the front door, the recursive step is one more room, and the loop ends when there are no doors left.

Cheat Sheet

• Use WITH RECURSIVE for variable-depth trees.
• Anchor member finds the starting rows.
• Recursive member joins the table back to the growing result.
• Add depth and sometimes path for clarity.
• Prefer UNION ALL unless you truly need deduplication.
• Protect against cycles, orphans, and recursion limits.

Practice Tasks

• Write a query that lists every employee under a chosen manager.
• Write the reverse query that returns all ancestors for one node.
• Add a depth limit and a cycle-safe visited-path check.