Loop Joins, More rows on top or bottom?

September 29, 2008 at 9:27 am (TSQL) (, , , )


I’ve seen this question come by in the web searches multiple times. The nested loop join is also called an iterative join. This is because it takes the rows from the inner part of the join and compares them through an iterative process (one-by-one) to the rows in the outer part of the join. So, if the optimizer has correctly chosen this operation for your query, you should see FEWER rows in the top, or outer, part of the join and MORE rows in the bottom, or inner, part of the join.

Take this query as an example (run against AdventureWorks2008):

SELECT *
FROM [Sales].[SalesOrderHeader] soh
JOIN [Sales].[SalesOrderDetail] sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.[SalesOrderID] = 47716

Here we have a single row from the SalesOrderHeader table and 55 rows from the SalesOrderDetail table. Here’s the execution plan:

This is a classic loop join. Now, if you see a loop join that’s reversed, with more rows on top or one where the same number of rows are on both sides of the join, that’s worth checking out as a problem. It could be missing or incorrect indexes, or, more likely, bad or out of date statistics. Do a statistics update, maybe with a full scan to see if that fixes the problem.

Update: I forgot I recorded a little video on this too. It’s available over at JumpStartTV

Update #2: As was pointed out in the comments below, I had the outer & inner labels swapped. I’ve updated the text to reflect the correction.

3 Comments

  1. Abram said,

    This is backwards. Outer table is on top, inner table is on bottom. Nested loop join takes the outer part and and compares to the inner part.

    For Each <— outer
    …For Each <– inner

  2. Abram said,

    Also, you may want to check your Jumpstart TV videos and SQL Server Exec Plan book because I think its backwards there as well.

  3. scarydba said,

    You are right. I do have the labels inner & outer swapped. Sorry about that. As to which one should have fewer rows, the outer part of the operation, as you correctly point out, the top, should have fewer rows, as I said.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: