28.3. Nested Set Model of Hierarchies
Since SQL is a set-oriented language, the nested set model is a better model for the approach discussed here. If you have used HTML, XML or a language with a block structure, then you understand the basic idea of this model. The lft and rgt columns (their names are abbreviations for “left” and “right,” which are reserved words in Standard SQL) are the count of the “tags” in an XML representation of a tree.
Imagine circles inside circles without any of them overlapping, the way you would draw a markup language structure. This has some predictable results that we can use for building queries, as shown in Figures 28.2, 28.3, and 28.4.
Figure 28.2.
Figure 28.3.
Figure 28.4.
If that mental model does ...
Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
