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.
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.