[thelist] SQL Server - INNER JOIN vs WHERE clause

[Joshua Olson]

> -----Original Message-----
> From: Chris at globet.com
> Sent: Thursday, September 01, 2005 6:24 AM
> 
> 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?

Chris,

Your question is a bit ambiguous.  By "explicit" do you mean the difference
between these two syntaxes?:

1.
SELECT table1.foo, table2.bar
FROM table1, table2
WHERE table1.pkid = table2.fkid

2.
SELECT table1.foo, table2.bar
FROM table1
INNER JOIN table2
WHERE table1.pkid = table2.fkid

Or, do you mean the difference between these two syntaxes?:

3.
SELECT table1.foo, table2.bar
FROM table1
INNER JOIN table2
WHERE table1.pkid = table2.fkid

4.
SELECT table1.foo, table2.bar
FROM table1
INNER JOIN table2
ON table1.pkid = table2.fkid

If you are talking about syntaxes 1 and 2, then avoid syntax 1.. it's just
good practice to use the JOIN keywords.

If you are asking about the differences between syntaxes 3 and 4, then
according to Rudy Limebeck [0], the two techniques are logically equivalent
but can have vastly different performance hits depending on how the parser
plans the query.  You'll need to consider two key factors when deciding
between the techniques:

1.  Which version produces a query plan that is better optimized.

2.  Which is more maintainable should the code need to be retrofitted later
to incorporate more joined (INNER or OUTER) tables.

[0] http://lists.evolt.org/archive/Week-of-Mon-19990531/087318.html

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/