Multiple tables in single "ON" clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple tables in single "ON" clause

Author

Topic

LonnieM
Starting Member

15 Posts

Posted - 2009-11-06 : 18:01:19

Hello All,

I have a query that uses 6 joins to produce a results set. It is in the form of:

Select A.field1, B.field2, C.field3, etc....
From Table A
Inner Join Table B
On A.field1 + A.field2 = B.field1 + B.field2
Inner Join Table C
On A.field1 + A.field2 = C.field1 + C.field2
Inner Join Table D
On A.field1 = D.field1 --this is needed to get D.field99
Inner Join Table E
On A.field1 + D.field99 = E.field1 + E.field2
Inner Join Table D
On A.field1 + D.field99 = F.field1 + F.field2

This query runs as expected and the results have been verified. But in looking back at it I confused as to how the SQL Server engine could process two different tables on the same side of the join statement. It seems as though this would not work because the order of processing the joins is left up to SQL Server.

Should this work?
Would this work in other SQL databases?

Is there a better way?....CTE’s etc….

Thank you,
Lonnie

sshelper
Posting Yak Master

216 Posts

Posted - 2009-11-18 : 00:01:20

Your query should work. SQL Server will know which tables to join first based on the ON conditions of your query. You can even check the order of the tables it joins by looking at the execution plan.

Regards,
SQL Server Helper
http://www.sql-server-helper.com/sql-server-2008/connection-error-1326.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources