Join operators, part 1 - terminology

  • Comments posted to this topic are about the item Join operators, part 1 - terminology


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • This was removed by the editor as SPAM

  • Nice question, not because it's really necessary to know the names, but for the explanation of where the names come from.

  • Got 2 out of 3 correct.

    I use leading and following as terminology for the merge join, but apparently that's not official 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • An interesting question. I guess I don't see where any of it is in the execution plan itself.

  • Thanks, all, for the kind words!

    sipas (11/20/2015)


    Nice question, not because it's really necessary to know the names, but for the explanation of where the names come from.

    I think that knowing the terminology is already important in itself.

    When you want to learn about performance optimization, you will read whitepapers and blogs, watch training videos or attend conferences. In all those situations, these terms will be used. So you will need to know what they mean. I have two more questions lined up on join operators, and I will use these terms in those questions.

    Ed Wagner (11/20/2015)


    An interesting question. I guess I don't see where any of it is in the execution plan itself.

    Not directly, but there are indirect references to it. A Hash Match join operator will always have a "Hash Keys (Build)" and a "Hash Keys (Probe)" property, to describe which column or columns from each input is/are used to drive the hash-based matching algorithm. And a Nested Loops join operator can sometimes have an "Outer References" property that tells which data from the outer input is referenced by the inner input.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Got 2 of 3 right, the Nested Loops join operator got me 😎

    But @ least learnt somthin new, thanx.

  • Got tripped up on the third one.

  • Though there is not official terminology for the merge operator, I would think Inner and Outer would suffice.

    Looking at the Execution Plan XML, for a merge operator, there will be an Inner node and an Outer node for the join columns. Those nodes should appear prior to the Residual node. Based on that, it makes sense to me to use the same terminology as for the Nested Loops operator. Inner and Outer would represent the same inputs as they do in the case of the Nested Loops.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I'm going back over questions I missed. Thanks for the good explanation about the join names.

Viewing 10 posts - 1 through 10 (of 10 total)

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