Most SQL developers hit a wall the first time they try to store a category tree or an organisational chart inside a relational database. Generally, flat tables handle rows well. But the moment you need to represent parent and child relationships across multiple levels, standard table structures start to fall apart.
To solve that problem, developers often use the nested set model. Joe Celko popularised this approach as a query-friendly way to store hierarchical data inside a single database table. And many SQL systems still rely on it today.
In this article, you’ll learn how nested sets work, how left and right values define each node’s position in the hierarchy, and how to query a tree without recursive joins. If you’re ready to get practical with hierarchical data in SQL, keep reading.
Flat tables have no built-in way to represent parent-child relationships, so tree structures become difficult to manage very quickly.
From what we’ve seen, many SQL developers initially try to brute-force tree queries with JOINs before realising relational tables aren’t suitable for deep tree layouts. Also, flat tables store rows independently, which makes parent and child relationships hard to process within multiple levels.
At the same time, relational databases handle structured row-based data extremely well. But categories, organisational charts, and nested navigation systems all rely on hierarchy, and standard tables provide no natural way to represent that depth.
As those hierarchies grow larger, query complexity increases quickly. Even three or four nested levels often require several JOINs, and performance usually declines with each added layer. SQL developers run into this limitation frequently when working with product categories, nested comments, or multi-level content systems.
So, before choosing a storage model, it helps to understand exactly where flat table frameworks begin struggling with hierarchical data.
Honestly, the first time you see a nested set tree with lft and rgt (left and right) values, it looks like someone spilled numbers across the table. In practice, the model assigns two numbers (lft and rgt) to every node, and those integers store the entire hierarchy inside a single flat table.
Two concepts explain how this works, and the following sections cover both concepts.
Getting your head around boundary values takes about ten minutes with a simple example.
Think of it like nesting Russian dolls, where each parent completely wraps around its children. In the same way, every node receives a left value and a right value, and those two integers define its position inside the parent-child structure.
For that layout to work correctly, the model follows strict numbering rules. A parent node always holds a lower left value than its child nodes, while its right value stays higher. As a result, every child node sits numerically inside its parent’s boundaries.
This framework allows developers to read the entire hierarchy without relying on additional JOINs.
The lft and rgt columns store the boundary integers each node receives during creation. Together, those values define the node’s exact position inside the nested structure.
For instance, if the difference between left and right values is 1, the node has no children. Likewise, a parent node with lft = 2 and rgt = 11 contains 4 child nodes.
You can calculate this through the formula (rgt – lft – 1) / 2, so (11 – 2 – 1) / 2 = 4. They also call these terminal nodes, and the lft, rgt values make them easy to spot.
Depth works the same way. A node at depth 2 has 2 ancestors whose boundaries fully contain it.
With that foundation in place, retrieving related nodes becomes an easy positional comparison, without the need to stack recursive calls or evaluate level number by level.
Neither model wins outright, as nested sets read data quickly, while adjacency lists remain much easier to update and maintain.
The adjacency list stores a parent ID inside each row, which keeps inserts and deletions simple. So many developers use this model first for hierarchical data. But the problem shows up in SQL requests. Pulling all the descendants of a given node requires recursive CTEs or multiple queries together.
By contrast, nested sets retrieve entire subtrees through a single boundary-comparison query. However, inserts and deletes require updates across many rows in the table (product category trees are the most common real-world example, and they map to this model perfectly).
The table below breaks down exactly how both methods handle each operation:
| Feature | Nested Sets | Adjacency List |
| Read subtree | Single query | Need Recursive CTE |
| Insert node | Updates many rows | Updates one row |
| Delete node | Updates many rows | Updates one row |
| Immediate children | Boundary comparison | Simple parent ID lookup |
| Hierarchy depth | Calculated from boundaries | Requires recursive traversal |
From our experience, adjacency lists usually work better for projects where the hierarchy changes frequently.
Bottom Line: Nested sets and adjacency lists both solve hierarchical storage differently, so the better option depends on how often the application reads, inserts, or updates tree data.
Most developers are surprised by how little SQL a nested set model needs to retrieve a full subtree. But once the boundary rules are in place, they can look up nodes at any level through simple positional comparisons.
The following two SQL request types cover the bulk of real use cases, and both are easy to follow:
To find all the descendants of a given parent node, developers select every child where lft falls between the parent’s lft and rgt values. That single query replaces multiple recursive JOINs. The logic may feel abstract at first, but the retrieval operation returns the full subtree immediately.
In practice, we’ve found that nested sets outperform recursive CTEs significantly on tables with more than 10,000 nodes. And adding ORDER BY lft to the output returns all child nodes in correct top-down tree order.
This is the code pattern for selecting all descendants:
| SQL Query: |
| SELECT child.name, child.lft, child.rgt FROM nested_sets AS child WHERE child.lft > parent.lft AND child.lft < parent.rgt ORDER BY child.lft; |
Quick Tip: Use SELECT COUNT to check how many descendants a given node has before running the full retrieval.
Indexed boundary values return moderately complex subtree queries in under 2 milliseconds.
However, if you want to find the full path from a root node to a given parent node, you’ll have to select all ancestors where lft is less than the target’s lft value (parent.lft < child.lft). Then filter where rgt is greater than the target’s rgt (parent.rgt > child.rgt) to confirm each ancestor wraps around it.
Code pattern for retrieving the full path from the root to the node:
| SQL Query: |
| SELECT parent.name, parent.lft, parent.rgt FROM nested_sets AS parent WHERE parent.lft < child.lft AND parent.rgt > child.rgt ORDER BY parent.lft; |
Finally, ordering by lft ascending gives you the exact root-to-node path in sequence. Also, the rgt group values confirm the wrapping at each level, and SELECT COUNT on the result gives you the exact depth.
Quick Tip: Use MAX on lft to locate the immediate parent within the returned stack of ancestors.
Getting inserts right the first time saves hours of debugging corrupted lft and rgt values later.
Generally, every new node requires its own lft and rgt integer values. Before inserting that node, developers also shift existing boundary values throughout the table (most tutorials skip this step, which is exactly why incorrect inserts often corrupt the entire tree structure).
The following code pattern shows the full process in MySQL and SQL Server:
Running these steps outside a transaction in MySQL or SQL Server can leave the table vulnerable. When an UPDATE fails midway, the integer sequence develops gaps, and those gaps break later queries on affected child nodes.
A transaction prevents that damage. So if one step fails, the database rolls back the full insert process and keeps the tree intact.
Boundary recalculation during node movement catches many developers off guard the first time they work with nested sets. Unlike basic inserts, moving a node to a new parent requires you to recalculate positional values among multiple parts of the hierarchy.
Specifically, two aspects of this operation are worth understanding before writing a single line of code.
Moving a node in nested sets is tricky because every structural change forces a recalculation of lft and rgt boundaries within multiple rows. If you fail to do this carefully, it will corrupt trees during inserts.
For this reason, you should recalculate boundary values for every ancestor and sibling in the affected branch after a move. That process looks very different from an adjacency list model, where you usually update only the parent ID column on a single row.
But nested sets don’t work that way. Each child node, immediate sibling, and ancestor in the subtree carries lft and rgt values tied to the node’s old position. So you must update all of them together.
Without careful sequencing, overlapping left and right values can corrupt large sections of the hierarchy. Once those boundaries stop matching correctly, subtree queries start returning incorrect parent-child relationships.
A stored procedure wraps the full node-movement logic in a single database transaction. That’s why every delete, insert, and boundary recalculation runs as one atomic unit, so the database can roll back the entire process cleanly if any step fails.
This approach also changes how safely you can handle these operations. For example, you implement the logic once in MySQL or SQL Server, test it thoroughly, and reuse it for every future node.
After that, users trigger the procedure with a single call, and the tree stays intact regardless of how complex the move is.
Now that inserts and node movements are clear, the next step is understanding where nested set trees usually break.
Anyone who has debugged a corrupted hierarchy already knows how important transaction discipline becomes in nested sets. Sometimes, even one missing rgt update can break descendant queries throughout the entire table.
Developers working with nested sets for the first time tend to fall into the same traps:
Pro Tip: Use BEGIN block and SELECT COUNT to check the tree before every structural change, so the tree stays clean. It’s the single most reliable habit to build when working with nested sets at any scale and a defensive pattern, too.
Not every project needs nested sets, and picking the wrong model for your use case creates more work down the line.
Joe Celko, who created and popularised the nested set model, recommended it specifically for stable hierarchies where applications frequently run subtree queries but rarely change the structure. So before you commit to one model, it’s worth mapping out how often your tree actually changes.
Hierarchies with stable parent-child relationships, like product categories, navigation menus, and organisational charts, fit this model well. Each node in the hierarchy gets an int column for its lft and rgt values when you create the row. Those columns keep depth calculations and subtree queries fast without recursive CTEs.
If your tree changes constantly, the write overhead of maintaining lft and rgt values across many rows may not be worth it. Because the adjacency list handles frequent structural changes with far less disruption to the stored data. Joe Celko addressed this tradeoff directly in his writing, and his guidance still holds up well in modern database implementations.
Simply put, nested sets work best when the group of elements in your hierarchy is stable and well defined. Plus, well-indexed int columns keep a select node or select child query fast, while positional values make leaf nodes easy to identify.
For those use cases, the model remains one of SQL’s most reliable structures you can implement.
The nested set model is a framework that feels complex on paper but becomes intuitive once you’ve worked through a real implementation. Left and right values, boundary comparisons, and transaction-wrapped inserts all follow a consistent logic once the tree layout clicks.
A string of failed manual updates or a null gap in the integer sequence can corrupt an entire hierarchy, which is exactly why we covered the transaction-based approach in this article. For stable categories like product trees or nested menus, nested sets consistently outperform recursive approaches at scale.
If you’re ready to implement a clean, query-efficient hierarchy in your database, DevelopersDex can help you. We work with development teams to build well-structured, high-performance database solutions.
Get in touch and let’s work through it together.
Company Reviews