SQL Server Performance

JOIN Approaches

Discussion in 'T-SQL Performance Tuning for Developers' started by Madhivanan, Sep 27, 2005.

  1. Madhivanan Moderator


    I see some SQL Programmers use this type of joins

    Select.....
    from table1 T1 join
    table2 T2 join
    table3 T3 join
    table4 T4
    on T4.col=T3.col
    on T3.col=T2.col
    on T2.col=T1.col
    where ........
    But I always use

    Select ....from
    table1 T1 join table2 T2 on T2.col=T1.col
    table2 T2 join table3 T3 on T3.col=T2.col
    table3 T3 join table4 T4 on T4.col=T3.col
    where .......
    Is there any significant performance differences?
    Which one is most preferrable?

    Madhivanan

    Failing to plan is Planning to fail
  2. dineshasanka Moderator

    fromhttp://www.sql-server-performance.com/tuning_joins.asp

    When you create joins using Transact-SQL, you can choose between two different types of syntax: either ANSI or Microsoft. ANSI refers to the ANSI standard for writing joins, and Microsoft refers to the old Microsoft style of writing joins. For example:

    ANSI JOIN Syntax

    SELECT fname, lname, department
    FROM names INNER JOIN departments ON names.employeeid = departments.employeeid

    Former Microsoft JOIN Syntax

    SELECT fname, lname, department
    FROM names, departments
    WHERE names.employeeid = departments.employeeid

    If written correctly, either format will produce identical results. But that is a big if. The older Microsoft join syntax lends itself to mistakes because the syntax is a little less obvious. On the other hand, the ANSI syntax is very explicit and there is no chance you can make a mistake.

    For example, I ran across a slow-performing query from an ERP program. After reviewing the code, which used the Microsoft JOIN syntax, I noticed that instead of creating a LEFT JOIN, the developer had accidentally created a CROSS JOIN instead. In this particular example, less than 10,000 rows should have resulted from the LEFT JOIN, but because a CROSS JOIN was used, over 11 million rows were returned instead. Then the developer used a SELECT DISTINCT to get rid of all the unnecessary rows created by the CROSS JOIN. As you can guess, this made for a very lengthy query. Unfortunately, all I could do was notify the vendor's support department about it.

    The moral of this story is that you probably should be using the ANSI syntax, not the old Microsoft syntax. Besides reducing the odds of making silly mistakes, this code is more portable between database, and eventually, I imagine Microsoft will eventually stop supporting the old format, making the ANSI syntax the only option


    ----------------------------------------
    Cast your vote
    http://www.geocities.com/dineshasanka/sqlserver05.html

    http://spaces.msn.com/members/dineshasanka
  3. FrankKalis Moderator

    No, both are equivalent. SQL Server semantically optimises this and produces identical execution plans. I guess, it boils down to what you think is more readable. To me, this is your second approach.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  4. Madhivanan Moderator

    Dinesh, both of them are ANSI Joins]

    Frank,
    Yes the second is more readable. If I use first method, then join columns should be specified from bottom-to-top order of the tables

    Madhivanan

    Failing to plan is Planning to fail
  5. FrankKalis Moderator

    Try it out


    SELECT c.*, c.*, od.*
    FROM Customers c
    JOIN Orders o
    JOIN [Order Details] od
    ON :confused:rderID = od.OrderID
    ON o.CustomerID = c.CustomerID

    SELECT c.*, c.*, od.*
    FROM Customers c
    JOIN Orders o
    JOIN [Order Details] od
    ON o.CustomerID = c.CustomerID
    ON :confused:rderID = od.OrderID


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  6. Madhivanan Moderator

    Yes Thats what I meant [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. Adriaan New Member

    And sometimes you need to do a 3-way join, where you need to name all participating tables before the ON clause.

    SELECT a.*, b.*, c.*
    FROM a
    INNER JOIN b
    INNER JOIN c
    ON a.col1 = b.col1
    AND a.col2 = c.col2
    AND b.col3 = c.col3

    But of course that is equivalent to:

    SELECT a.*, b.*, c.*
    FROM a
    INNER JOIN b ON a.col1 = b.col1
    INNER JOIN c ON a.col2 = c.col2 AND b.col3 = c.col3

    ... and even to this:

    SELECT a.*, b.*, c.*
    FROM a
    INNER JOIN b ON a.col1 = b.col1
    INNER JOIN c ON a.col2 = c.col2
    WHERE b.col3 = c.col3

    It will depend on your exact query which expression is the most appropriate - I don't think there would be much of a performance boost/loss for any variation.
  8. Madhivanan Moderator

    Adriaan, I think your first method wont work so as the second query of Frank [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  9. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Adriaan, I think your first method wont work so as the second query of Frank [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">[:0][xx(][B)] You're absolutely right, Madhivanan!<br /><br />
  10. FrankKalis Moderator

    Only one to go, Adriaan. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  11. Adriaan New Member

    Done! This is already post #1001 ...
  12. satya Moderator

    Check it out:<br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10587>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10587</a> [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  13. indshri Member

    Guys -- Correct me if I am wrong but one small difference is that ANSI provides FULL OUTER join whereas there is no such syntax in the old style. IT has to be combination of individual sql's. This has been explained in "inside sql server 7.0" book.
  14. FrankKalis Moderator

    Correct, in the ANSI SQL 92 Compliant Syntax where you JOIN in the ON clause there is support for FULL OUTER JOIN, while in the older (SQL 89, I think) JOIN syntax where you JOIN in the WHERE clause there is no such thing as a FULL OUTER JOIN.

    INNER JOIN, however, are absolutely in compliance with SQL 92 when specified either in the ON or the WHERE clause.


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  15. Adriaan New Member

    Use UNION if you can't use FULL JOIN:

    SELECT a.key, b.key
    FROM a INNER JOIN b ON a.key = b.key

    UNION
    SELECT a.key, b.key
    FROM a LEFT JOIN b ON a.key = b.key
    WHERE b.key IS NULL

    UNION
    SELECT a.key, b.key
    FROM a RIGHT JOIN b ON a.key = b.key
    WHERE a.key IS NULL
  16. Adriaan New Member

    That must have been the first time I've ever used RIGHT JOIN intentionally![<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Of course I could have avoided that by using<br /><br />....<br />UNION<br />SELECT a.key, b.key<br />FROM b LEFT JOIN a ON b.key = a.key<br />WHERE a.key IS NULL<br /><br />

Share This Page