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

Dig Deeper on Oracle and SQL

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.

Please create a username to comment.