OUTER JOIN table limit?

I came across this statement from ASP.NET forum : "..There is a limit to the level for OUTER JOIN ANSI SQL limit is four after that you may get strange results. ..." . I did a little research but without getting clear answer from the SQL92 standard itself. I am wondering whether I can get help about this in SQL Server 2005 implementation here.

I put this question in another way, How many tables can we use in OUTER JOIN(or INNER JOIN) in SQL Server 2005?

Thanks.

There is no limit in the ANSI SQL standard or SQL Server. In fact, the ANSI SQL standard doesn't talk about such limits anywhere. They provide specifications on the syntax and how it should work etc. Note that a particular database implementation can have limits imposed. SQL Server 2005 for example has a maximum limit of 256 table references in a SELECT statement. So you can end up in some situation where the query optimizer cannot produce a plan due to insufficient resources in the system if the query is too complex and contains large number of table references. See below example for SQL Server 2005 which allows more than 4 outer joins:

with t(i)
as (
select 1
)
select *
from t as t1
left join t as t2
left join t as t3
left join t as t4
left join t as t5
left join t as t6
left join t as t7
left join t as t8
left join t as t9
left join t as t10
on t10.i = t9.i
on t9.i = t8.i
on t8.i = t7.i
on t7.i = t6.i
on t6.i = t5.i
on t5.i = t4.i
on t4.i = t3.i
on t3.i = t2.i
on t2.i = t1.i

|||

as far as sql server is concerned there is no limitation.

it could have been a limitation from data access tier

e.g. dataset

|||

Hello:

I was pointed to this "Three-Way Joins and Beyond" in SQL Pperformance Tuning by Peter Gulutzan and Trudy Pelzer.

Can I get some explaination for this statement: "You can expect the DBMS optimizer to start going wrong if five or more joins are in the query (until recently Microsoft's admitted limit was four). "

Is there anything I miss here?

This book was published on September 10, 2002.

Thank you.

|||

That article makes several incorrect assumptions and looks like the authors are not well informed about SQL Server. For example, the support for recognizing transitive predicates has been in the product since SQL Server 7.0. See link below:

http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part9/sqc13.mspx?mfr=true

I didn't go through all the chapters but the first page itself has several errors and doesn't apply to SQL Server. Performance of joins involving large number of tables can be a problem depending on the resources and the query plan. This has also been improved considerably in the product for every release starting from SQL 6x. If you are looking for querying tips for SQL Server, you may want to look at some of the new Inside SQL Server 2005 series for example.

|||

Hi Umachandar:

Thank you for answering this question. I used multiple outer joins to extract a dataset from my database. That table limit statement could pose a serious problem to my query if it were true for SQL Server 2000 or 2005.