Nested Loops Join

SQL Server supports three physical join operators: Nested Loops, Merge and Hash.

The nested loops join takes each row from the outer table and compares it to each row in the inner table and returns rows that are satisfied by the join predicate.

In pseudo-code, it shall look something like the following.

for each row Row1 in the outer table Table1
     for each row Row2 in the inner table Table2
          if Row1 joins with Row2 (predicate)
               return (Row1, Row2)

Below is an example of a select statement in which the optimizer should use a nested loops join operator.

select * from AdventureWorks.HumanResources.Employee e
inner join AdventureWorks.Person.Contact c on e.ContactID = c.ContactID

The nested loops join performance is directly related to the number of rows in the outer table times the number of rows in the inner table. As the number of rows increase in the inner table, the cost grows quickly.

The nested loops joins is said to work best for small data sets and or where there is an index on the inner table.