Join Performance - SQL Server 2005

[techgnome]

USING: SQL Server 2005

I'm used to seeing Joins constructed using what I'll call the "Traditional" format:

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:

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

[abhijit]

I started off with Oracle 7.3 and for me the traditional format is

SELECT * FROM TABLE1 A, TABLE2 B
WHERE A.PRIMARY_KEY = B.FOREIGN_KEY



I am still struggling to come to terms with the INNER JOIN syntax.

[techgnome]

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

[abhijit]

I tried out that syntax in Oracle.
For some reason syntax #2, illustrated by you fails in Oracle 9i.