After identifying a set of adjacent join nodes, the optimizer uses the available statistics to estimate the cardinality (the number of rows in the table or intermediate result) and selectivity (percentage of rows a predicate returns) for each subtree of the join nodes. It then uses the following criteria to determine the join order:
The subtree with the lowest estimated cardinality is taken first. The SQL engine's cost manager estimates the cardinality of each subtree by multiplying table cardinality by the selectivity of the predicates applied to the table.
The subtree that has the lowest estimated join cardinality (number of output rows produced by a join with the first subtree) is taken second. When determining join cardinality, the optimizer considers whether there is a join condition between the two subtrees. It gives preference to subtree pairs that have join conditions.
The subtree with the next lowest estimated join cardinality is taken next, and so on.
The subtree with the lowest estimated cardinality is taken first. The SQL engine's cost manager estimates the cardinality of each subtree by multiplying table cardinality by the selectivity of the predicates applied to the table.