[thelist] SQL Server - INNER JOIN vs WHERE clause
Paul Cowan
evolt at funkwit.com
Thu Sep 1 08:50:15 CDT 2005
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
More information about the thelist
mailing list