Difference between two join method

  • Hi guys,
    Can anybody tell me the difference between the two below examples and why I would use one over the other?

    Example 1

    LEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
    ON PCI.ItemValue = GPExcess.ParameterId
    AND GPExcess.ParameterName = 'MedicaExcessAmount'

    Example 2

    LEFT OUTER JOIN (SELECT * FROM Company.schema.GeneralParameter
                                      WHERE ParameterName = 'MedicaExcessAmount'
                                     )GPExcess
    ON PCI.ItemValue = GPExcess.ParameterId

    Both examples work, just wondering what the differences are.
    Thanks

  • Check execution plans of both queries.

    See if you can spot any difference.

    _____________
    Code for TallyGenerator

  • Well, the second one is using a derived table where clearly none is needed. The other is just a JOIN. Effectively, any JOIN is actually a SELECT * under the covers, so you can use a syntax such as this and you'll still arrive at the same place. The question is, why? It's added muck in a query with no benefits. It's certainly possible that having lots of this pattern may even cause problems for the optimizer as it attempts to unpack a non-standard syntax. There might be reasons to do a derived table like this (say, for example, in order to do some aggregation in the sub-select and then JOIN that to the other table(s)), but to just do it because you can... I sure wouldn't recommend it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The second one is harder to read and takes more typing, and that's about all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I tend to prefer keeping join criteria separate from filtering criteria so would generally go with a third option

    LEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
    ON PCI.ItemValue = GPExcess.ParameterId
    WHERE GPExcess.ParameterName = 'MedicaExcessAmount'

    Generally I find it easier to read.
    As long as the execution plans are the same then which you choose will be down to personal preference and/or company standards.

  • crmitchell - Wednesday, October 10, 2018 8:06 AM

    I tend to prefer keeping join criteria separate from filtering criteria so would generally go with a third option

    LEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
    ON PCI.ItemValue = GPExcess.ParameterId
    WHERE GPExcess.ParameterName = 'MedicaExcessAmount'

    Generally I find it easier to read.
    As long as the execution plans are the same then which you choose will be down to personal preference and/or company standards.

    It can, however, make quite the difference as to what is returned for outer joins.

    --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)

  • crmitchell - Wednesday, October 10, 2018 8:06 AM

    I tend to prefer keeping join criteria separate from filtering criteria so would generally go with a third option

    LEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
    ON PCI.ItemValue = GPExcess.ParameterId
    WHERE GPExcess.ParameterName = 'MedicaExcessAmount'

    Generally I find it easier to read.
    As long as the execution plans are the same then which you choose will be down to personal preference and/or company standards.

    As written, this is an inner join - there's nothing in the code to allow null values of GPExcess.ParameterName. Adjusting this syntax to permit rows on the left with no matching rows on the right makes the code clunky, harder to skim read.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Yes your right the GPExpess is on the RHS so as Jeff pointed out that would effectively turn it into an INNER JOIN.
    That's what I get for not trying it out before posting I guess.

  • crmitchell - Wednesday, October 10, 2018 8:06 AM

    I tend to prefer keeping join criteria separate from filtering criteria so would generally go with a third option

    LEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
    ON PCI.ItemValue = GPExcess.ParameterId
    WHERE GPExcess.ParameterName = 'MedicaExcessAmount'

    Generally I find it easier to read.
    As long as the execution plans are the same then which you choose will be down to personal preference and/or company standards.

    However, since this is an outer join, you've changed the meaning of the query and the results returned. And moving the filter to the WHERE and adding 'OR GPExcess.ParameterName IS NULL' does not have the same behaviour either.

    Table1  t1  LEFT OUTER JOIN Table2 t2 on t1.ID = t2.ID and T2.colour = 'red'
    returns all rows from T1 and, if there's a matching row in t2 that is red, returns that as well

    Table1  t1  LEFT OUTER JOIN Table2 t2 on t1.ID = t2.ID
    WHERE T2.colour = 'red' OR T2.colour IS NULL
    returns all rows from T1 that either had a colour of red or no colour at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

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