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.

Outer Join problems

This may or may not be due to my lack of knowledge on the use of outer join. I am getting the error:

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'IncidentId'.

From my query:

SELECT Incident_Incidents.RelatedIncidentId,

Incident_Incidents.RelatedType,

Incident.Subject,

Incident.RecordId

From Incident_Incidents, Incident

left outer join IncidentId on Incident.IncidentId = Incident_Incidents.IncidentId

where Incident.IncidentId = 12838

Any ideas?

SELECT

Y.RelatedIncidentId,

X.RelatedType,

X.Subject,

X.RecordId

From

Incident_Incidents X

LEFT OUTER JOIN Incident Y

ON

X.IncidentId = Y.IncidentId

where X.IncidentId = 12838

|||Sorry not to clear on what you've got going on here, Tried running it with no luck|||

Here is a sample of some real code; I might of have mistaken some of your field name. It looks like your trying to left outer join on a column name instead of a table name, which will not work.

SELECT

P.iPLANID

FROM

PLANS P

INNER JOIN Units M

ON M.iPlanid = P.iPlanid

|||

my guess:

SELECT Incident_Incidents.RelatedIncidentId,

Incident_Incidents.RelatedType,

Incident.Subject,

Incident.RecordId

From Incident_Incidents

left outer join Incident on Incident.IncidentId = Incident_Incidents.RelatedIncidentId

where Incident.IncidentId = 12838

Out Join Syntax

Hello,

I need to write and Ansi Outer Join that has 2 columns from the same table.

I keep getting

Server: Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dim_person.person_key" could not be bound.

here is what the code looks like. any help is appreciated.

select ...

from dim_day_of_year
, dim_company
, dim_currency
, dim_account
, dim_person
, ods_conference
left outer join fact_usage_alloc fua1 on ods_conference.conf_key=fua1.conf_key
left outer join fact_usage_alloc fua2
on dim_person.person_key=fua2.requestor_person_key

where...from ...
, ods_conference
left outer
join fact_usage_alloc fua
on fua.conf_key = ods_conference.conf_key
and fua.requestor_person_key = dim_person.person_key|||That certainly makes sense to me but produces a syntax error :mad:

left outer join fact_usage_alloc fua
on fua.conf_key=ods_conference.conf_key
and on fua.requestor_person_key=dim_person.person_key
where fua.company_key = dim_company.company_key...........

Thanks for the reply|||you're welcome

you can't say "and on"|||hmmm. Still getting the bind error. I even cut and pasted your example in and I get the same thing. I didn't realize mssql was so particular :S|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)|||Mixing non ansi and ansi forms is just a bad idea

Plus, unless I missing it, your outter join tables don't seem to be joing to anything

Plus, you really should get into good formatting, it will make your like so much easier

SELECT ColumnList
FROM dim_day_of_year a
INNER JOIN dim_company b
ON b.col = ?.col
INNER JOIN dim_currency c
ON c.col = ?.col
INNER JOIN dim_account d
ON d.col =?.col
INNER JOIN dim_person e
ON e.col = ?.col
INNER JOIN ods_conference f
ON f.col = ?.col
LEFT JOIN fact_usage_alloc g
ON e.conf_key = f.conf_key -- This one makes no sense
LEFT JOIN fact_usage_alloc h
ON d.person_ke = g.requestor_person_key -- Nor does this one|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)

You funny

The message you have enetered is too short|||You funnyno, i was serious -- i bet the error goes away because of how the FROM clause is parsed

the explicit JOINS are probably being interpreted first, before being combined with the comma-delimited tables

sort of like * and / are done before + and -

thus, in this local context:
ods_conference
left outer
join fact_usage_alloc fua
on fua.conf_key = ods_conference.conf_key
and fua.requestor_person_key = dim_person.person_keythis explains the error message -- "dim_person.person_key" could not be bound

dim_person hasn't been parsed as a table yet|||Thanks for the effort guys. This was a quick test to see how easy it would be to convert a legacy system to MSSQL. I think we got a portion of the answer.

Thanks Again.sql

OSQL returns error code of 1 - intermittently

Hello,
I've written an program that runs OSQL to set up a database. I got a weird
situation yesterday where OSQL returns an error level of 1 and didn't
generate any output file at all. The command I use is:
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
Normally, this command runs OK and produces a .log file. Why would it
return 1 and no log file?
I call OSQL from a Borland Builder C++ program.
Any ideas?
Regards,
Robert
PS: I ran the OSQL command straight after I installed MSDE and started up
SQL Server.Hi,
you are assuming some things:
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
-E are you really able to use integrated authentication, if not you
have to provide a user name and a password. Perhaps you are running
your program under a special account which isn=B4t priviledged in the
database ?!
-i Are you in the right folder that SOQL can find the query file while
executing the file from the program ?
(missing -S) If you don=B4t specify a server, the default behaviour is
to go to the local server, the default instance, to the default port
1433. If this is not valid, you have to define the server/indtance,port
e=2Eg. Servername\instancename,Port.
HTH, Jens Suessmeyer.|||Hi,
Thanks for your reply.
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
-E are you really able to use integrated authentication, if not you
have to provide a user name and a password. Perhaps you are running
your program under a special account which isnt priviledged in the
database ?!
I think so. I am running as (machine) administrator when I execute OSQL
-i Are you in the right folder that SOQL can find the query file while
executing the file from the program ?
The SetupRTEDatabase.sql script is in the same directory as the calling
program.
(missing -S) If you dont specify a server, the default behaviour is
to go to the local server, the default instance, to the default port
1433. If this is not valid, you have to define the server/indtance,port
e.g. Servername\instancename,Port.
This is OK. I run OSQL pretty much straight after I do an install of SQL
Server. I don't change any of the default settings.
Regards,
Robert|||I've fixed the problem.
As it turns out, I was running the program in the wrong folder after all.
OSQL was being run (indirectory) from a Setup program which ran on a network
drive.
I did a ChangeDirectory to the path with the SetupRTEDatabase.sql script and
everything worked fine.
"Robert Wheadon" <robert.wheadon@.monitorbm.co.nz> wrote in message
news:uC$01qrTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Thanks for your reply.
> OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
> -E are you really able to use integrated authentication, if not you
> have to provide a user name and a password. Perhaps you are running
> your program under a special account which isnt priviledged in the
> database ?!
> I think so. I am running as (machine) administrator when I execute OSQL
> -i Are you in the right folder that SOQL can find the query file while
> executing the file from the program ?
> The SetupRTEDatabase.sql script is in the same directory as the calling
> program.
> (missing -S) If you dont specify a server, the default behaviour is
> to go to the local server, the default instance, to the default port
> 1433. If this is not valid, you have to define the server/indtance,port
> e.g. Servername\instancename,Port.
> This is OK. I run OSQL pretty much straight after I do an install of SQL
> Server. I don't change any of the default settings.
> Regards,
> Robert
>