Join order

The order that tables are joined in a query is extremely important. A poor join order can cause query performance to decline noticeably.

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
The optimizer can choose one of the following join orders:
  • Join customer to orders. Join the result to items.
  • Join orders to customer. Join the result to items.
  • Join customer to items. Join the result to orders.
  • Join items to customer. Join the result to orders.
  • Join orders to items. Join the result to customer.
  • Join items to orders. Join the result to customer.

For an example of how the database server executes a plan according to a specific join order, see Example of query-plan execution.