Post reply

Filter on INNER JOIN

  • November 9, 2007 at 7:28 am

    #62881

    Hi all

    Is it good practice to use an AND clause on the INNER JOIN statement or is it best to have it sat in the WHERE clause.

    For example I need to join one table (customer_portfolio) with another (company_portfolio) where the portfolio ID is the primary key and foreign key but I also want to be able to

    filter on the customer_portfolio table only those customer who have a specific customer_id. Normally I would do this with the following...

    INNER JOIN customerofportfolio cu ON (cu.id = c.portfolio_id)

    WHERE(cu.KUNDE_NR = '962020')

    But I am sure I read somewhere that it was best to do it the following way. Is this true?

    INNER JOIN customerofportfolio cu ON (cu.id = c.portfolio_id) AND (cu.KUNDE_NR = '962020')

    Thanks

    Tryst

  • November 9, 2007 at 7:30 am

    #749838

    It's my understanding that the query optimizer treats them both the same.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 10, 2007 at 9:06 pm

    #750079

    For clarity, I would put the condition in the WHERE clause. As far as execution, you can run both queries and get the execution plan for both. That should clear up any confusion. I know when I ran a similar query against Northwind using Order and [Order Details] the execution plans and costs, as the previous poster indicated, were identical.

    K. Brian Kelley
    @kbriankelley

  • November 11, 2007 at 6:52 am

    #750092

    And, to be sure, it DOES matter where you put the filters in the case of an OUTER JOIN.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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