Join Performance - SQL Server 2005
USING: SQL Server 2005
I'm used to seeing Joins constructed using what I'll call the "Traditional" format:
Code:
SELECT *
FROM tbl1
INNER JOIN tbl2
ON tbl1.field1 = tbl2.field1
INNER JOIN tbl3
ON tbl2.field2 = tbl2.field2
Apparently there is an "Alternative" format that allows you to nest the joins:
Code:
SELECT *
FROM tbl1
INNER JOIN tbl2
INNER JOIN tbl3
ON tbl2.field2 = tbl2.field2
ON tbl1.field1 = tbl2.field1
Besides the maintenance ick factor that I encounter when ever I see this construct... does any one know if there is a performance difference between the two?
I've got a query that is using this alternative construct, before I got in there deciphering what the joins really are, and re-arranging them to my liking, I'm curious to know if there would be any benefit to do so.
-tg
Re: Join Performance - SQL Server 2005
Yeah, I'm aware of that too... but that's not my concern... it's the nested joins (not just inner but I've got some LEFT ones that are done the same way...) I went ahead and re-arranged the query in question... no change in performance.... it would seem that my bottleneck seems to be the part of the join that has 128,000+ items on one side and 66,000+ items on the other....
-tg
Re: Join Performance - SQL Server 2005
I tried out that syntax in Oracle.
For some reason syntax #2, illustrated by you fails in Oracle 9i.