Is there a performance difference?
-
between: SELECT * FROM Blah1 as T1 INNER JOIN Blah2 as T2 ON T1.ID=T2.ID INNER JOIN Blah3 as T3 ON T1.Id=T3.ID ; vs. SELECT * FROM Blah1 as T1 ,Blah2 as T2,Blah3 as T3 WHERE T1.ID=T2.ID AND T1.ID=T3.ID ; I've never really understood the advantage of the first syntax, and I'm always using the second syntax. Marc MyXaml Advanced Unit Testing
-
between: SELECT * FROM Blah1 as T1 INNER JOIN Blah2 as T2 ON T1.ID=T2.ID INNER JOIN Blah3 as T3 ON T1.Id=T3.ID ; vs. SELECT * FROM Blah1 as T1 ,Blah2 as T2,Blah3 as T3 WHERE T1.ID=T2.ID AND T1.ID=T3.ID ; I've never really understood the advantage of the first syntax, and I'm always using the second syntax. Marc MyXaml Advanced Unit Testing
The advantage of the first syntax is legibility. It visually and logically separates the evaluations that are specifically used to define the join(s) from the evaluations that are only used to filter the joined results.
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
-
between: SELECT * FROM Blah1 as T1 INNER JOIN Blah2 as T2 ON T1.ID=T2.ID INNER JOIN Blah3 as T3 ON T1.Id=T3.ID ; vs. SELECT * FROM Blah1 as T1 ,Blah2 as T2,Blah3 as T3 WHERE T1.ID=T2.ID AND T1.ID=T3.ID ; I've never really understood the advantage of the first syntax, and I'm always using the second syntax. Marc MyXaml Advanced Unit Testing
If you have access to SQL Query Analyzer, type each statement and then Ctrl + L (Display Extimated Execution Plan). This will display what steps the server will do and when you move the mouse pointer over each step it will tell you the estimated cost in CPU cycles, using the database current stats.
-
between: SELECT * FROM Blah1 as T1 INNER JOIN Blah2 as T2 ON T1.ID=T2.ID INNER JOIN Blah3 as T3 ON T1.Id=T3.ID ; vs. SELECT * FROM Blah1 as T1 ,Blah2 as T2,Blah3 as T3 WHERE T1.ID=T2.ID AND T1.ID=T3.ID ; I've never really understood the advantage of the first syntax, and I'm always using the second syntax. Marc MyXaml Advanced Unit Testing
The first syntax is far superior in my mind, for the following reasons: 1) legibility 2) ease of maintenance - easily add/remove a join/condition 3) flexibility - it is easier to construct complex queries this way, especially those containing outer joins and/or complex joins. I do think it is possible that the 1st syntax will lead to better optimized queries, because it gives the optimizer explicit information on what is a join and what is part of the WHERE clause. In a simple query it probably makes no difference, but once the query becomes complex, I'm sure it helps the query optimizer to know these things. Final point on legibility - I usually write my queries something like this
SELECT *
FROM Blah1 as T1
INNER JOIN Blah2 as T2 ON T1.ID=T2.ID
INNER JOIN Blah3 as T3 ON T1.Id=T3.IDwhich is much more legible.
-
between: SELECT * FROM Blah1 as T1 INNER JOIN Blah2 as T2 ON T1.ID=T2.ID INNER JOIN Blah3 as T3 ON T1.Id=T3.ID ; vs. SELECT * FROM Blah1 as T1 ,Blah2 as T2,Blah3 as T3 WHERE T1.ID=T2.ID AND T1.ID=T3.ID ; I've never really understood the advantage of the first syntax, and I'm always using the second syntax. Marc MyXaml Advanced Unit Testing
Hi all. For what it's worth, the WHERE clause syntax for creating joins was part of the ASNI SQL/86 standard. The INNER JOIN / OUTER JOIN syntax became part of the ASNI SQL/92 standard. In addition to the other comments made, the JOIN syntax also allows for more functionality, such as the FULL OUTER JOIN supported in some RDBMSs.