order sequence in join statements

  • Hi ,

      I have a basic question. Can you please tell me the order sequence in the following select statement.

            select a.col1,a.col2,b.col2 from table1 a inner join table2 b on a.col1 = b.col1 where a.col1 > 100.

    Which one will be first executed? the "on condition" or "where condition"?

    Thanks,

    Sridhar!!

     

  • Joins are always executed before the where criteria.

    In queries with more than 1 join, you can sometimes make the query more efficient by moving some of the criteria to the join statement.

    For example, if you have the following query:

    Select *

    From Table1 As t1 Inner Join Table2 As t2 On t1.t1ID = t2.t1ID

    Inner Join Table3 As t3 On t2.t2ID = t3.t2ID

    Where t1.SomeValue > 10

    You can improve performance by moving the criteria to the first join because it will limit the number of rows resulting from the first join making the second join faster. Like so:

    Select *

    From Table1 As t1 Inner Join Table2 As t2 On t1.t1ID = t2.t1ID And t1.SomeValue > 10

    Inner Join Table3 As t3 On t2.t2ID = t3.t2ID


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • However, this is no exact science, and there are more factors than just semantics.

    For inner joins, there is no difference for the endresult if stuff goes into the ON clause or the WHERE clause.

    Many times the optimizer will choose to execute queries like this exactly the same.

    SELECT ...

    FROM tabA JOIN tabB

    ON tabA.id = tabB.id

    WHERE tabA.someValue > 100

    SELECT ...

    FROM tabA JOIN tabB

    ON tabA.id = tabB.id

    AND tabA.someValue > 100

    The only way to know is to check the query plans generated.

    Having said that - join order sequence does matter for outer joins, but that's another thing than this question.

    =;o)

    /Kenneth

  • Joins occurr before the where condition.

    And when multiple joins exist the occurr top to bottom, inside to outside.

    So say you have (IJ = Inner Join)

    SELECT

     ...

    FROM

     tA

    IJ

     tB

     IJ

      tC

     ON

      tC.Col = tB.Col

    ON

     tB.Col = tA.Col

    IJ

     tD

    ON

     tD.Col = tC.Col

    IJ

     tE

     IJ

      tF

     ON

      tF.Col = tE.Col

    ON

     tF.Col = tC.Col

    WHERE

     ...

    GROUP BY

     ...

    HAVING

     ...

    ORDER BY

     ...

    So in the above this occurrs

    tC joined to tB results joined to tA results joined to tD results joined to results of (tF joined to tC).

    Where is then processed, then Group By, then Having (Consider Having the Where of a Group By), Then Order By.

    Also good to note is that in the join tB to tC the On clause for those does not recognize the existance of tA and in the Join of tF to tE the On clause does not recognize the other resultsets.

     

    Also note with Inner Join more times than not it will not make a difference in speed but that does mean it wouldn't hurt to check. With Outer joins it can change your entire filnal result.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply