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

Limits on number of tables in a FROM clause

In IBM's DB/2 for OS/390, their documentation lists 15 as the maximum number of tables in the FROM clause of a SELECT. Elsewhere, their documentation states that up to 225 tables may be specified in a VIEW, SELECT, etc.

1. Isn't that a bit contradicting?
2. What is the ANSI SQL standard?

The reason I am asking is that I have working examples of SELECTs, in both MS Access and MS SQL Server, where I have upwards of 30 self-joins.

Short example:

SELECT PriTab.Field1, PRiTab.Field2 
 FROM PriTab, RelTab RelTab_1, RelTab_2
  WHERE PriTab.PKey = RelTab_1.Key1
   AND PriTab.PKey = RelTab_2.Key1
   AND RelTab_1.Key2 = 'X'
   AND RelTab_2.Key2 = 'Y'
In the above example, I would generate the SQL to add an incremented table alias name for as many of the related table values that were to be searched on.


I cannot speak to DB/2's limitations nor can I speak to whether or not its documentation contradicts itself.

As far as the SQL standard -- or standards -- I can say that I don't believe there is a limit to the number of tables in a SELECT statement. A limit to the number of tables would be a physical limitation of a DBMS, whereas the SQL standards are more or less logical and do not stipulate physical implementation.

I would like to offer an alternative to your example that would not require such a large number of joins, and consequently might improve performance considerably:

select PriTab.Field1, PriTab.Field2
  from PriTab, ( select Key1
                   from RelTab
                   where Key2 = 'X'
                 intersect
                 select Key1
                   from RelTab
                   where Key2 = 'Y' ) NewRelTab
  where PriTab.Pkey = NewRelTab.Key1;
This query uses the INTERSECT set operator to find those Key1 values that have RelTab rows with both 'X' and 'Y' values. Whether or not this query will perform better than your original query will depend on your DBMS and possibly the number of tables you are joining.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close