November 6, 2009 at 4:06 pm
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 (reliably) 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
November 6, 2009 at 4:12 pm
lmeinke (11/6/2009)
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 (reliably) 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
Would help if we could see the DDL for the tables involved. Just looking at what you provided doesn' really help much. Looking at the query, why are you doing things like this: On A.field1 + D.field99 = E.field1 + E.field2.
November 6, 2009 at 7:15 pm
lmeinke (11/6/2009)
But in looking back at it I'm confused as to how the SQL Server engine could process (reliably) 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?
Hey Lonnie,
Yes it should work. The query optimizer is free to process the joins in any order, so long as the plan produces correct results. Specifying columns from multiple tables in the join adds constraints to the join order, since the engine must have the values of every column participating in the join before performing the join. So, the plan produced will always honour the dependencies built in to the join specifications, but the exact order of the joins, along with the join strategy used, will otherwise depend on estimated costs and built in transformation rules, in the normal manner.
As far as asking whether there is a better way, the answer is probably yes. As Lynn implied, it is unusual to perform joins based on concatenated values from different tables - but the unusual thing is the concatenation, not the fact that column values come from different tables.
A more usual construct might be ...A JOIN B ON A.col1 = B.col1 AND A.col2 = C.col2 for example.
If that doesn't help, post some additional detail to enable us to understand the problem better.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply