Ermin Pijuk

Many of the datasets we touch are naturally hierarchical-category trees, nested folders, and component breakdowns. They look simple, but the moment you need to roll everything up or drill everything down, the workarounds creep in: scripts, exports, glue code, and a slower system. Expressing this cleanly in SQL isn’t always easy, but it is absolutely possible, and doing it in the database is usually cleaner, more reliable, and far more efficient. With a small set of well‑structured queries, you can avoid fragile plumbing, keep the logic close to the data, and get results that are fast, consistent, and easy to maintain.   Why Hierarchies Are Tricky and How SQL Helps? Hierarchical datasets are everywhere: category trees, nested folders, component breakdowns, and they tend to get hard exactly when you need “everything under X” or to roll numbers up across levels. A single join can’t chase an unknown number of layers, so teams reach for scripts and glue code, adding complexity and latency. SQL can express multi-level traversal and aggregation close to the data, which keeps logic consistent, reduces round-trips, and usually runs faster. Key points: One join is not a traversal: a join links one level; hierarchies can be arbitrarily deep, so the query must keep following children until none remain. Code-side traversal adds overhead: N+1 queries, extra round-trips, duplicated rules, and brittle caching. What the database needs: the parent–child link, a starting node (or set of roots), and optional constraints like depth limits or filters; with that, it can walk and aggregate efficiently.  Approaches to Hierarchical Queries in SQL There isn’t one “best” way to model and query hierarchies; the right choice depends on how often you read vs. write, how deep/wide the tree is, and what your database supports. Below is a compact overview to help you pick a starting point. Practical notes: Default to recursive CTEs when supported; they keep logic close to the data and are portable across modern engines. If reads vastly outnumber writes and trees are stable, consider closure tables.  For simple prefix queries, extensions like PostgreSQL ltree or SQL Server hierarchyid improve ergonomics. Index the hierarchy and related facts. Add an index on employees(manager) and keep a primary key on employees(id). Index sales(employee_id). For closure tables, index both ancestor_id and descendant_id and consider a composite unique key on (ancestor_id, descendant_id). For materialized paths, index the path column for prefix searches. Guard depth and handle cycles. Always cap recursion when possible and include cycle protection. This is one of the areas where engines differ. PostgreSQL: add a level column and a WHERE level < N, detect cycles by tracking a visited array or path. PostgreSQL 14+ also supports the standard CYCLE clause. SQL Server: cap depth with OPTION(MAXRECURSION N). Detect cycles by tracking a path string or use hierarchy to constrain the structure. Oracle: CONNECT BY supports NOCYCLE and CONNECT_BY_ISCYCLE, and you can limit LEVEL. MySQL 8 and SQLite: use WITH RECURSIVE with an explicit depth check and a visited path to avoid cycles. Due to its practicality and prevalence on various platforms, in this article, we will work with Recursive CTE. Example dataset — employees and sales We will use a small but uneven hierarchy that includes multiple roots. Only leaf employees record sales. Manager totals will later be computed as the sum of their subtrees. Employees Sales Querying hierarchies in PostgreSQL Recursive patterns in SQL are compact and readable once you see them. We will start with a minimal CTE that walks from each root to every leaf and prints a clean path. Then we can reuse the same shape for filtering and rollups later. List every root to leaf path WITH RECURSIVE paths AS (SELECT id, manager, employee::text AS PATH FROM employees WHERE manager IS NULL UNION ALL SELECT e.id, e.manager, p.path || ' -> ' || e.employee FROM employees e JOIN paths p ON e.manager = p.id) SELECT p.path FROM paths p LEFT JOIN employees ch ON ch.manager = p.id WHERE ch.id IS NULL ORDER BY p.path; Why this works: The anchor starts at the roots where the manager is NULL and initializes the path with the employee name. The recursive step follows children and appends names to build the path. The anti-join filters to the leaves by keeping rows that have no child in the employees. Result preview on the sample data: FERNANDO -> GUANYU FERNANDO -> LANCE FERNANDO -> VALTTERI LEWIS -> GEORGE -> CARLOS -> YUKI LEWIS -> GEORGE -> CHARLES -> ESTEBAN LEWIS -> GEORGE -> CHARLES -> PIERRE LEWIS -> LANDO -> OSCAR MAX -> DANIEL -> ALEX -> NICO -> KEVIN MAX -> DANIEL -> LOGAN MAX -> SERGIO Aggregating sales across the hierarchy A common need is to report sales per employee, with managers inheriting the totals of everyone below them. We can do this by starting from actual sales at the leaves and bubbling those amounts up through the manager chain, then aggregating per employee. WITH RECURSIVE up AS (SELECT e.id, e.manager, s.amount FROM sales s JOIN employees e ON e.id = s.employee_id UNION ALL SELECT m.id, m.manager, u.amount FROM up u JOIN employees m ON m.id = u.manager) SELECT emp.id, emp.employee, emp.role, COALESCE(sum(u.amount), 0) AS total_sales FROM employees emp LEFT JOIN up u ON u.id = emp.id GROUP BY emp.id, emp.employee, emp.role ORDER BY total_sales DESC, emp.employee Why this works: The anchor takes each sale and tags it with the selling employee and their manager. The recursive step moves that amount one level up by replacing the current employee with their manager, and keeps going until it reaches a root. The final SELECT left joins every employee to this stream of amounts and sums them, so leaves get their own sales, and managers get the sum of their entire subtree. Sorting by total_sales gives a clear top-to-bottom view. Practical notes: Add indexes on employees(manager) and sales(employee_id) for good performance Recursion is expressed as an anchor UNION [ALL] recursive term. Prefer UNION ALL for performance. Use UNION only if you need to remove duplicates at each step. COALESCE ensures employees with no subtree sales show as zero rather than null Result preview on the sample data: Conclusion Hierarchical queries don't have to mean complex application code or fragile workarounds. Modern SQL provides powerful tools, particularly recursive CTEs, that handle tree traversal elegantly and efficiently right where your data lives. The key takeaways: Keep it simple: Start with recursive CTEs for most hierarchical needs. They're portable, readable, and sufficient for most use cases. Optimize when needed: Only reach for specialized solutions (closure tables, ltree) when you have specific performance requirements and measurement data to justify the added complexity. Think set-based: Instead of iterating row by row, let the database engine handle the recursion efficiently in a single query. Index strategically: A simple index on the parent column often provides dramatic performance improvements. By mastering these patterns, you transform what could be dozens of application queries and complex logic into clean, maintainable SQL that performs well and scales naturally with your data. The next time you encounter a hierarchy, whether it's an org chart, category tree, or component breakdown, you'll have the tools to query it confidently and efficiently. "Hierarchical and recursive queries in SQL" Tech Bite was brought to you by Ermin Pijuk, Software Engineer at Atlantbh. (more…)