SQL Server Performance

Criteria on INNER JOIN vs. WHERE

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by apskallen, Jan 30, 2007.

  1. apskallen New Member

    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

  2. khtan New Member

    No different. It is just a matter of convention.

    Personally, i would put the condition in WHERE rather than in ON condition.


    KH
  3. Roji. P. Thomas New Member

    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
  4. apskallen New Member

    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
  5. Roji. P. Thomas New Member

  6. FrankKalis Moderator

    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>
  7. Roji. P. Thomas New Member

  8. FrankKalis Moderator

    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>

Share This Page