Opinions on join syntax? 1

[mowog74]

I typically use the JOIN keyword when joining tables, e.g.:

SELECT [...]
FROM Orders o
INNER JOIN OrderStatus os ON rderStatusId = os.Id
WHERE o.Id = 5


But lately I've been seeing (esp. in Books Online) syntax like:

SELECT [...]
FROM Orders o, OrderStatus os
WHERE rderStatusId = os.Id
AND o.Id = 5

Is there any functional difference between the two methods? Is one preferred? I like the first method for readability, but I get the impression that maybe the second method is more ANSI compliant. Any thoughts?

 

[tlbroadbent]

Check the article on Microsoft's SQL Web Site explaining why the ANSI style joins are preferred.

http://www.microsoft.com/sql/using/tips/development/July23.asp

It is ironic, that MS is moving toward ANSI style but the documentation is laced with old style joins. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mowog74]

Thanks Terry. Also, I think I had it backwards--after reading the article you suggested and others it looks like the first syntax in my original post (using the JOIN keyword) is ANSI style. Thanks for straightening me out!

 

[fluteplr]

You should use the ansi joins for production code. I have to admit that when I am doing a quick adhoc query from query analyser I frequently use the where style, it just seems faster to type.

 

[tlbroadbent]

I failed to note that I prefer the ANSI syntax for at least two reasons.

1) It separates the JOIN criteria from the WHERE criteria in the query.
2) It shows clearly the relatiobships between the tables and views in the query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[gaffonso]

I would add that ANSI style joins can be faster than "natural" joins (doing joins using only the WHERE clause). Presumably this is because the specialized join statements allow the query processor to do optimization that it otherwise cannot.

- Gary