Example of query-plan execution
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.
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
- 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.