Join operators, part 1 - terminology

Question

Join operators, part 1 - terminology

Hugo Kornelis

SSC Guru

Points: 64790
More actions
November 19, 2015 at 12:04 am

#313236

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/
November 19, 2015 at 1:45 am

This was removed by the editor as SPAM

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

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

sipas Hall of Fame Points: 3828 More actions · Answer 1

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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/

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

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

But @ least learnt somthin new, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

November 23, 2015 at 1:55 pm

#1841848

Got tripped up on the third one.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

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