Databases Reference
In-Depth Information
Taking benefit from the fact that both of its inputs are sorted on the join predicate, a
Merge Join simultaneously reads a row from each input and compares them. If the rows
match, they are returned. If the rows do not match, the smaller value can be discarded
because, since both inputs are sorted, the discarded row will not match any other row on
the other input table.
This process continues until one of the tables is completed. Even if there are still rows on
the other table, they will clearly not match any rows on the fully-scanned table, so there is
no need to continue. Since both tables can potentially be scanned, the maximum cost of a
Merge Join is the sum of both inputs.
If the inputs are not sorted, the Query Optimizer it is not likely to choose a Merge Join,
although you can test this and see what the Query Optimizer does if we force a Merge
Join. If you run the following query, you will notice that it uses a Nested Loops Join, as
shown in Figure 2-28.
SELECT
*
FROM
HumanResources
.
Employee
AS
e
INNER
JOIN
Person
.
Contact
AS
c
ON
e
.
ContactID
=
c
.
ContactID
Listing 2-30.
Figure 2-28:
A Nested Loops Join.
