This topic contains an example of a query with a SELECT
statement that calls for a three-way join and describes one possible
query plan.
The following SELECT statement calls for a three-way join:
SELECT C.customer_num, O.order_num
FROM customer C, orders O, items I
WHERE C.customer_num = O.customer_num
AND O.order_num = I.order_num
Assume also that no indexes are on any of the three tables. Suppose
that the optimizer chooses the customer-orders-items path and
the nested-loop join for both joins (in reality, the optimizer usually
chooses a hash join for two tables without indexes on the join columns). A
query plan in pseudocode shows the query plan,
expressed in pseudocode. For information about interpreting query
plan information, see Report that shows the query plan chosen by the optimizer.
Figure 1. A
query plan in pseudocode
for each row in the customer table do:
read the row into C
for each row in the orders table do:
read the row into O
if O.customer_num = C.customer_num then
for each row in the items table do:
read the row into I
if I.order_num = O.order_num then
accept the row and send to user
end if
end for
end if
end for
end for
This procedure reads the following rows:
All rows of the customer table once
All rows of the orders table once for each row of the customer table
All rows of the items table once for each row of the customer-orders pair
This example does not describe the only possible query plan. Another
plan merely reverses the roles of customer and orders:
for each row of orders, it reads all rows of customer,
looking for a matching customer_num. It reads the same number
of rows in a different order and produces the same set of rows in
a different order. In this example, no difference exists in the amount
of work that the two possible query plans need to do.