Databases Reference
In-Depth Information
Merge Join
Now let's take a look at a Merge Join example; run the following query, which returns
the name of each customer that is categorized as a store. The execution plan is shown in
Figure 2-27.
SELECT
Name
FROM
Sales
.
Store
AS
S
JOIN
Sales
.
Customer
AS
C
ON
S
.
CustomerID
=
C
.
CustomerID
WHERE
C
.
CustomerType
=
N'S'
Listing 2-29.
Figure 2-27:
A Merge Join example.
One difference between this and a Nested Loops Join is that, in a Merge Join, both input
operators are executed only once. You can verify this by looking at the properties of
both operators, and you'll find that the number of executions is 1. Another difference is
that a Merge Join requires an equality operator and its inputs sorted on the join predicate.
In this example, the join predicate has an equality operator, is using the
CustomerID
column, and both clustered indexes are ordered by
CustomerID
, which is their
clustering key.
