Problem
Will I obtain the same result from queries A and B below, regardless of performance? What about options C and D?
-- A
select *
from a left join b
on <blahblah>
left join c
on <blahblan>
-- B
select *
from a left join c
on <blahblah>
left join b
on <blahblan>
-- C
select *
from a join b
on <blahblah>
join c
on <blahblan>
-- D
select *
from a join c
on <blahblah>
join b
on <blahblan>
Asked by Just a learner
Solution #1
The order of INNER joins does not important. The queries will return same results, as long as you change your selects from SELECT * to SELECT a.*, b.*, c.*.
Yes, the sequence matters for (LEFT, RIGHT, or FULL) OUTER joins – and (updated) things are much more complicated.
Because outer joins are non-commutative, an LEFT JOIN b is not the same as an LEFT JOIN a.
Outer joins aren’t associative either, therefore in the situations when both (commutativity and associativity) qualities are required:
a LEFT JOIN b
ON b.ab_id = a.ab_id
LEFT JOIN c
ON c.ac_id = a.ac_id
is equivalent to:
a LEFT JOIN c
ON c.ac_id = a.ac_id
LEFT JOIN b
ON b.ab_id = a.ab_id
but:
a LEFT JOIN b
ON b.ab_id = a.ab_id
LEFT JOIN c
ON c.ac_id = a.ac_id
AND c.bc_id = b.bc_id
is not the same as:
a LEFT JOIN c
ON c.ac_id = a.ac_id
LEFT JOIN b
ON b.ab_id = a.ab_id
AND b.bc_id = c.bc_id
Another (hopefully simpler) illustration of associativity. Consider it like this: (a LEFT JOIN b) LEFT JOIN c:
a LEFT JOIN b
ON b.ab_id = a.ab_id -- AB condition
LEFT JOIN c
ON c.bc_id = b.bc_id -- BC condition
A LEFT JOIN (b LEFT JOIN c) is identical to this:
a LEFT JOIN
b LEFT JOIN c
ON c.bc_id = b.bc_id -- BC condition
ON b.ab_id = a.ab_id -- AB condition
We only have “nice” ON conditions because we have “nice” ON conditions. The equality checks ON b.ab id = a.ab id and c.bc id = b.bc id do not use NULL comparisons.
ON a.x = b.x or ON a.x = 7 or ON a.x LIKE b.x or ON (a.x, a.y) = (b.x, b.y) = (b.x, b.y) = (b.x, b.y) = (b.x, b.y) = (b.x, b.y) = (b.x, b.y) = (b.x
If however, any of these involved IS NULL or a function that is related to nulls like COALESCE(), for example if the condition was b.ab_id IS NULL, then the two queries would not be equivalent.
Answered by ypercubeᵀᴹ
Solution #2
If you try to join C to a field from B before joining B, you’ll get the following error:
SELECT A.x,
A.y,
A.z
FROM A
INNER JOIN C
on B.x = C.x
INNER JOIN B
on A.x = B.x
Your query will fail, thus the order is crucial in this case.
Answered by Teo J.
Solution #3
It doesn’t work for ordinary Joins. The execution plan for TableA join TableB is the same as for TableB join TableA. (so your C and D examples would be the same)
for left and right joins it does. TableA left Join TableB is different than TableB left Join TableA, BUT its the same than TableB right Join TableA
Answered by Diego
Solution #4
The Oracle optimizer determines the order in which tables are joined for an inner join. Only in simple FROM clauses does the optimizer choose the join order of tables. Oracle documentation is available on their website. In the case of the left-right outer join, the most popular solution is correct. For each table, the optimizer determines the best join order as well as the best index. The order in which the indexes are joined can have an impact on which index is the best choice. If a table is the inner table, the optimizer can choose an index as the access path, but not if it is the outer table (and there are no further qualifications).
Only in simple FROM clauses does the optimizer choose the join order of tables. The optimizer picks the join order for most JOIN keyword joins since they are flattened into simple joins.
For outer joins, the optimizer does not choose the join order; it uses the order stated in the statement.
The optimizer considers the following factors while deciding on a join order: Each table’s dimensions Each table’s indices are listed below. Whether a table’s index is useful in a specific join sequence For each table in each join order, the amount of rows and pages to be scanned.
Answered by Saumyojit Das
Post is based on https://stackoverflow.com/questions/9614922/does-the-join-order-matter-in-sql