How Many Kinds Of Joins Are There?

Quite So

There are three in SQL Server, not counting the Adaptive Join which is just a placeholder for either Nested Loops or Hash Joins.

If you look closely at each of those joins, you’ll see all sorts of interesting things pop up.

Hopefully none of them will be Sorts.

Nested Loops has:

• Unordered Prefetch
• Ordered Prefetch
• And might be Optimized!

Merge has:

• One to many
• Many to many
• With a Bitmap!

Hash has:

• Residuals
• “Perfect” hashes
• With a Bitmap!

But going even further, there’s stuff that isn’t exposed about Hash Joins.

Internally, they might be In-Memory, Grace, or Recursive. You can’t see that anywhere in a query plan, but it’s out there.

This Is Jeopardy

There are things I’d much rather you know and understand about joins, of course.

But the next time someone asks you what types of Joins SQL Server supports, you can have a much longer answer.

Thanks for reading!

Brent says: and then there’s remote joins, which aren’t really a separate kind of join, but just using a join hint to declare which side the join happens on. But I know that you, dear reader, are going to come up with a gazillion edge case examples of other kinds of joins in the comments. That’s how you roll.