Criteria on INNER JOIN vs. WHERE | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Criteria on INNER JOIN vs. WHERE

Hi,
What is most efficient: SELECT *
FROM Company a
INNER JOIN Store s
ON a.companyID = s.companyID
INNER JOIN Department d
ON s.storeID = d.storeID
WHERE d.type = ‘A’ or this one: SELECT *
FROM Company a
INNER JOIN Store s
ON a.companyID = s.companyID
INNER JOIN Department d
ON s.storeID = d.storeID
AND d.type = ‘A’ /Mats
No different. It is just a matter of convention. Personally, i would put the condition in WHERE rather than in ON condition.
KH
With INNER JOINs it doesnt matter. But with outer joins its a different story. I’d recommend putting the join condition on the ON clause and the filter condition on the where clause. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
quote:Originally posted by Roji. P. Thomas With INNER JOINs it doesnt matter. But with outer joins its a different story. I’d recommend putting the join condition on the ON clause and the filter condition on the where clause.

Thanks for your response. Is your recommendation valid for OUTER JOINs also? Is it better to have the filter condition on the WHERE clause than having it on the ON clause? Thanks, Mats
For outer joins, you get different results if you move the conditions from WHERE to ON.
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Btw, not only this, but when you’re running comp level 9.0 you’ll get a:<br /><pre id="code"><font face="courier" size="2" id="code"><br />USE Northwind<br />SELECT *<br /> FROM db:confused:rders o, [Order Details] od<br /> WHERE :confused:rderID =* od.OrderID<br /><br />Msg 4147, Level 15, State 1, Line 3<br />The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. <br />It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). <br />In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.<br /></font id="code"></pre id="code"><br />That’s a quite compelling reason to use th ON clause, methinks. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Frank, we are NOT talking about using the old style Join syntax! Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
I know. [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br />Still I think this sentence from the error message above is very descriptive. <br /><br />"It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN)." [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
]]>