Learn T-SQL Querying

Nested Loops joins

A Nested Loops join is a non-blocking operator. In a Nested Loops join, a row is fetched from the Outer Table, and the Inner Table is searched for a matching row. SQL Server loops on the Inner Table until no more rows are found, then it loops on the Outer Table. Because the number of iterations of the inner loop is determined by the number of rows in the Outer Table, SQL Server will generally choose the smaller of the two input to be the Outer Table in order to minimize the cost of the join. Also, since the outer table is the driver of the algorithm, the rows will be output from the Nested Loops join in the same order as they are input from the outer table.

The following diagram depicts the operation of a Nested Loops join: ...