[thelist] SQL Server - INNER JOIN vs WHERE clause

[Paul Cowan]

On 1/09/2005 11:24 AM +0100 Chris at globet.com wrote:
> Can anyone offer opinion on the relative merits of using an explicit
> INNER JOIN in select queries rather than implying the join in the WHERE
> clause. Is there a performance gain in one or the other (or any other
> benefits either way), or is it just down to preference?

There's (in my experience) a surprisingly huge readability/maintainability 
gain for the ANSI JOIN syntax, in the case you mention (INNER JOIN); that 
is the only difference, at least in SQL 2000. The query plans produced will 
(should?) be identical (or so claims the SQL Server "Bible", Kalen 
Delaney's "Inside Microsoft SQL Server 2000", and if Delaney says it that's 
good enough for me) and the output would be identical. Note that this isn't 
necessarily true in earlier versions of SQL -- I have seen occasions where 
SQL 7 would produce different query plans for the 2 queries.

Also, note my use of the words "in the case you mention" -- in the case of 
non-inner joins, they're not the same.
	FROM authors
	LEFT OUTER JOIN titleauthor ON
		....
is the same as
	FROM authors, titleauthor
	WHERE
		(authors.au_id *= titleauthor.au_id)
but they're not ALWAYS identical -- in fact, there are certain 
circumstances where results can be produced with the OUTER JOIN ANSI syntax 
that the old-style *= join operator simply is not capable of producing (see 
chapter 7 of the abovementioned book for a better example and explanation 
than I could come up with).

The fact that there are potentially circumstances where you will more or 
less be forced to use the ANSI-style syntax for outer joins is enough for 
me to suggest using that syntax for ALL joins (if you have a choice, of 
course) so as to not have to introduce inconsistency. Also, as I mentioned, 
I consider them substantially more 'stylistically preferable'. Otherwise, 
it shouldn't make a lick of difference!

Cheers,

Paul