Post reply

INNER JOINS

  • July 27, 2007 at 2:27 am

    #153713

    Comparing the client statistics the inner join with criteria in the join seemed to be faster by about 1 sec on +-10000 records on my machine over an average of 10 runs

  • July 27, 2007 at 2:28 am

    #722798

     Correct Approach

    SELECT [ClientNam] FROM dbo.Client C

    INNER JOIN dbo.ContactInfo CI ON CI.[ClientID] = C.[ClientID] AND CI.[IsValid] = 0

    WHERE

    C.[IsNew] = 0

  • July 27, 2007 at 2:32 am

    #722800

    The execution plan is exactly the same

  • July 27, 2007 at 2:46 am

    #722802

    As you already found out, the execution plan is exactly the same. The reason for this is that the Query Optimizer realises that both queries request the same data and then optimizes the way how to look up the data.

    If you look closely on the details of the execution plan you probaly see when the filter CI.[IsValid] = 0 is executed. My guess would be that it happens during the join.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • July 27, 2007 at 5:08 am

    #722819

    It actually would vary if the last table is used to join with a seperate 3rd table. The results will vary. It depends on the business logic that is expected. And most of the times, the IsVAlid in the joining clause will give less results as compared to where clause, if data for such a condition exists.

    regards,

    P

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

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