Databases Reference
In-Depth Information
Figure 2-26:
A Nested Loops Join with a filter on the outer table.
Note that the outer input is, again,
SalesPerson
, but this time it's not using an index;
the new predicate is using the
TerritoryID
column which is not included in any index,
and so the Query Optimizer decides to do a Clustered Index Scan instead. The filter on
the
SalesPerson
table is asking for
TerritoryID
equal to 1, and only three records
qualify this time. As a result, the Clustered Index Seek, which is the operator on the inner
input, is executed only three times. You can verify this information by looking at the
properties of each operator, as we did for the previous query.
To recap briefly, in the Nested Loops Join algorithm, the operator for the outer input will
be executed once, and the operator for the inner input will be executed once for every
row that qualifies on the outer input. The result of this is that the cost of this algorithm
is proportional to the size of the outer input multiplied by the size of the inner input. As
such, the Query Optimizer is more likely to choose a Nested Loops Join when the outer
input is small and the inner input has an index on the join key. This join type can be
especially effective when the inner input is potentially large, as only a few rows, indicated
by the outer input, will be searched.
