Databases Reference
In-Depth Information
In this case, the
Contact
table is sorted on the join predicate, but
Employee
is not. If
you're curious, you can force a Merge Join using a hint, as in the following query; the
Query Optimizer will introduce a Sort operator to sort
Employee
on
ContactID
, as
shown in Figure 2-29.
SELECT
*
FROM
HumanResources
.
Employee
AS
e
INNER
JOIN
Person
.
Contact
AS
c
ON
e
.
ContactID
=
c
.
ContactID
OPTION
(
MERGE
JOIN)
Listing 2-31.
Figure 2-29:
Plan with a hint to use a Merge Join.
As a summary, given the nature of the Merge Join, the Query Optimizer is more likely to
choose this algorithm when faced with medium to large inputs, where there is an equality
operator on the join predicate, and their inputs are sorted.
