12.12. SQL: Recursive Queries
Hierarchical data structures are found quite often in business. Some common examples are organizational structures with departments and subdepartments, and bill-of-material information involving assemblies and subassemblies. Recursion provides a powerful way to query such structures.
In SQL, recursive queries involve a three-phase process:
Create an initial result set (sometimes known as the anchor)
Recursively add results from further queries to the initial result set
Carry out a final query on the accumulated results
Recursion generally requires some termination condition. Here, the termination is implicit: the recursion ends when there are no more results to add in Phase 2. Particular SQL implementations may provide ...
Get Information Modeling and Relational Databases, 2nd 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.
