Databases Reference
In-Depth Information
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
JOIN
Sales
.
Customer
AS
Cu
ON
I
.
CustomerID
=
Cu
.
CustomerID
WHERE
Cu
.
CustomerType
=
'I'
Listing 1-7.
By looking at the resultant execution plan, shown on Figure 1-8, you can see that the
Query Optimizer is not using the same join order as that specified in the query; it found a
more efficient one instead. The join order as expressed in the query is (
Person.Contact
JOIN
Sales.Individual
)
JOIN
Sales.Customer
. However, you will see from the
plan shown in Figure 1-8 that the Query Optimizer actually chose the join order (
Sales.
Customer
JOIN
Sales.Individual
)
JOIN
Person.Contact
.
Figure 1-8:
Execution plan for query joining three tables.
You should also notice that the Query Optimizer chose a Merge Join operator to
implement the join between the first two tables, then a Hash Join operator to join the
result to the
Person.Contact
table.
Just to experiment, the query shown in Listing 1-8 shows the same query, but this time
using the
FORCE
ORDER
hint to instruct the Query Optimizer to join the tables in the
exact order indicated in the query. Paste this query into the same query window in
