Optimizing Database Performance
Nested Loops
The Nested Loops physical operator performs the Inner Join, Left Outer Join, Left Semi Join, and Left Anti Semi Join logical operations.
Nested loops joins perform a search on the inner table for each row of the outer table, typically using an index. Microsoft® SQL Server™ 2000 decides, based on anticipated costs, whether to sort the outer input in order to improve locality of the searches on the index over the inner input.
Any rows that satisfy the (optional) predicate in the Argument column are returned (as applicable based on the logical operation being performed).