Andy Brownsword takes a peek at the three most common types of join operators, plus a bonus:
When reviewing our execution plans we’ll see joins executed using different operators. The type of operator is chosen based on the data that’s available to join and how the optimiser wants to execute it.
In this post we’ll take a look at what the operators are, when they are used, and how they work. These are the operators we’ll cover:
- Nested Loop Joins
- Merge Joins
- Hash Match Joins
- (Bonus) Adaptive Joins
Read on for a quick overview of which works best when.