Joe Celko's Trees and Hierarchies in SQL for Smarties

CHAPTER 4 Nested Set Model of Hierarchies

TREES ARE OFTEN drawn as “boxes-and-arrows” charts, which tend to lock your mental image of a tree into a graph structure. Another way of representing trees is to show them as nested sets. It is strange that this approach was overlooked for so long among SQL programmers. Many of us are old enough to have used The Art of Computer Programming (Donald E. Knuth, ed 3, vol 1, Boston, Addison-Wesley, 1997) in college as our textbook, and we should remember this representation of trees on page 312, Figure 20.

Because SQL is a set-oriented language, this is a better model for the approach discussed here. Let us define an organizational chart table to represent the hierarchy and people in our sample organization. ...