About the optimizer's choice of join order

The optimizer chooses the optimal join order as well as the optimal index for each table.

The join order can affect which index is the best choice. The optimizer can choose an index as the access path for a table if it is the inner table, but not if it is the outer table (and there are no further qualifications).

The optimizer chooses the join order of tables only in simple FROM clauses. Most joins using the JOIN keyword are flattened into simple joins, so the optimizer chooses their join order.

The optimizer does not choose the join order for outer joins; it uses the order specified in the statement.

When selecting a join order, the optimizer takes the following into account:

Note: Derby does transitive closure on qualifications. For details, see Transitive closure.
Related concepts
About the optimizer's choice of access path
About the optimizer's choice of join strategy
About the optimizer's choice of sort avoidance
About the system's selection of lock granularity
About the optimizer's selection of bulk fetch