Q
Problem solve Get help with specific problems with your technologies, process and projects.

Recursion in SQL

One of the toughest SQL problems is supporting recursive (bill of material) relationships. For example, a part...

consists of many other parts; each part can go into many other higher level parts. Some DBMSs have recently added functions to provide support for such relationships. Can BOM recursion be supported in basic SQL, i.e. SQL that does not have special support for recursion? Does one have to go to application code to achieve support for it?

If you design your recursive relationship using the adjacency model, where each row has a foreign key to its parent row, then the answer is no, there is no recursion in basic SQL. There might be something in a recent SQL Standard, but what really matters is whether it's included in real-life database systems, and as far as I know, only Oracle among the established vendors supports it, through a proprietary SQL extension.

If you design your recursive relationship with the nested set model, however, then the answer is yes. First off, let me caution you that the nested set model is not for the faint of heart, but it is well worth the effort if you have recursion to many levels or to an unknown number of levels. Have a look at the SearchDatabase article Trees in SQL by Joe Celko.

For More Information


This was last published in October 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close