Databases Reference
In-Depth Information
Nested Loops Join
Let's start with a query listing employees who are also sales persons. This creates the plan
in Figure 2-23, which uses a Nested Loops Join:
SELECT
e
.
EmployeeID
FROM
HumanResources
.
Employee
AS
e
INNER
JOIN
Sales
.
SalesPerson
AS
s
ON
e
.
EmployeeID
=
s
.
SalesPersonID
Listing 2-27.
Figure 2-23:
A Nested Loops Join.
The input shown at the top in a Nested Loops Join plan is known as the
outer input
and the one at the bottom is the
inner input
. The algorithm for the Nested Loops Join
is very simple: the operator used to access the outer input is executed only once, and the
operator used to access the inner input is executed once
for every record that qualifies on
the outer input
. Note that, in this example, the plan is scanning a non-clustered index
instead of the base table for the outer input. Since there is no filter on the
SalesPerson
table, all of its 17 records are returned and so, as dictated by the algorithm, the inner input
(the Clustered Index Seek) is executed 17 times - once for each row from the outer table.
