Joe Celko's Trees and Hierarchies in SQL for Smarties

CHAPTER 9 Proprietary Extensions for Trees

AS YOU CAN see from the examples given earlier in this book, you very quickly get into recursive or procedural code to handle trees. Because the single-table adjacency list model is popular, several vendors have added extensions and academics have proposals to handle tree traversal in SELECT statements.

9.1 Oracle Tree Extensions

Oracle has CONNECT BY PRIOR and START WITH clauses in the SELECT statement to provide partial support for reachability and path enumeration queries. The START WITH clause tells the engine the value of the root of the tree. The CONNECT BY PRIOR clause establishes the edges of the graph. The function LEVEL gives the distance from the root to the current node, starting at 1 for ...