SQL Server 2000General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
The former is the newer style, SQL-92 JOIN syntax, where the latter is "old style" notation.
There is no accounting for taste, but I prefer the newer style, as it separates the predicates used to define JOIN criteria from WHERE predicates used to select rows from the resultant JOINs. If I have a complex WHERE clause, I like to keep it focused on row selection rather than JOIN definition.
The old style begins to show problems when used in OUTER JOINS (*= syntax). There, true ambiguities can result depending on the order of WHERE clause evaluation. Removing these ambiguities was the main reason why the new syntax was proposed.
I'd love to see that reference, or examples of queries which perform differently.
I certainly haven't done an exhaustive analysis, but every time I do look, I can see no difference in the query plans between old-style and new-style JOIN syntax. My bible, "Inside SQL Server 2000", states that there is no difference in performance and that "...behind the scenes, the operations are the same...".
Whether there was or wasn't a difference, I would still advocate the new style - it's just more readable IMO.
"Use the new ANSI JOIN syntax instead of the old style joins. The new join syntax has a slight performance advantage over the old way of using the WHERE clause for a join. The new syntax also tends to have better readability and should become the norm in your Transact-SQL coding."
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928††fax (360) 902-6200