Does the join order matter in SQL?

Question

Disregarding performance, will I get the same result from query A and B below? How about C and D?

----- Scenario 1: 
-- A (left join)
select *
from   a left join b
           on <blahblah>
       left join c
           on <blahblan>


-- B (left join)
select *
from   a left join c
           on <blahblah>
       left join b
           on <blahblan>  

----- Scenario 2:   
-- C (inner join)
select *
from   a join b
           on <blahblah>
       join c
           on <blahblan>


-- D (inner join)
select *
from   a join c
           on <blahblah>
       join b
           on <blahblan>

What's ``? are you joining A to B and A to C, or are you joining A to B and B to C? — beny23, Mar 08 '12 at 08:57
Hi Beny, the code in my question is an abstraction. I'm not concerned on joining A to B or A to C, I just want to know will the syntax like that will provide identical results. — Just a learner, Mar 08 '12 at 09:22

ypercubeᵀᴹ · Accepted Answer · 2013-11-16T21:36:23.020

311

For INNER joins, no, the order doesn't matter. The queries will return same results, as long as you change your selects from SELECT * to SELECT a.*, b.*, c.*.

For (LEFT, RIGHT or FULL) OUTER joins, yes, the order matters - and (updated) things are much more complicated.

First, outer joins are not commutative, so a LEFT JOIN b is not the same as b LEFT JOIN a

Outer joins are not associative either, so in your examples which involve both (commutativity and associativity) properties:

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 equivalent to:

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) associativity example. Think of this as (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

This is equivalent to a LEFT JOIN (b LEFT JOIN c):

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

only because we have "nice" ON conditions. Both ON b.ab_id = a.ab_id and c.bc_id = b.bc_id are equality checks and do not involve NULL comparisons.

You can even have conditions with other operators or more complex ones like: 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) and the two queries would still be equivalent.

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.

edited Nov 16 '13 at 21:36

answered Mar 08 '12 at 09:24

ypercubeᵀᴹ

113,259
19
174
235

4

It's more correct to say that the outer join is associative as long as neither predicate can be satisfied by a row in which all columns from one table are NULL, than to say that it's associative as long as the predicates don't involve IS NULL or 'a function that is related to nulls'. One can easily imagine a predicate that satisfies the former description but not the latter, like `a.somecol > 0 OR b.someothercol > 0`; associativity could fail for that condition. – Mark Amery Nov 17 '13 at 11:03
1

But yeah, I think that it's technically true to say that OUTER JOIN is associative as long as the predicate doesn't satisfy either of the conditions I describe here: http://stackoverflow.com/questions/20022196/are-left-outer-joins-associative/20022925#20022925 (the first of which also breaks associativity for INNER JOINs, but is such a cheap and obvious approach to breaking it that perhaps it's not worth mentioning.) It's also worth pointing out that the most common kind of JOIN - JOINing on a foreign key - doesn't satisfy either of those conditions and thus is nice and associative. – Mark Amery Nov 17 '13 at 11:06
2

@MarkAmery Thank you, I was having a hard time structuring my sentences on that point (and I have already upvoted that answer of yours ;) – ypercubeᵀᴹ Nov 17 '13 at 11:10
ypercube i have a `INNER JOIN` and a following `LEFT JOIN`. Does it work like that first the query will `Filter` the records on the base of `INNER JOIN` and then will apply `LEFT JOIN` to the `Filtered` records? – Muhammad Babar Feb 12 '15 at 13:09
1

In fact, all join types *are* associative, as specified by the SQL standard and according to mathematical definitions of associativity, but they don't *appear* associative because rearranging the parentheses requires moving the `ON` clause (i.e. the "join specification") to a new location. This is only syntax, though. If you use relational algebra notation (where the join specification is placed below the join operator), then associativity becomes more evident. Your argument only displays that outer joins are not *commutative*, which is correct – Lukas Eder May 09 '15 at 15:51
For inner joins, you mentioned that order of the joins doesn't affect the results, but can it affect the efficiency of the query? – Tyler Crompton May 29 '16 at 08:45
@ypercubeᵀᴹ Does the order of ON matter? For example if I join two tables Table A join Table B on A.id = B.id is it same as if I order ON B.id = A.id ? Thanks – Roxy'Pro Feb 28 '19 at 11:57
SQL non-natural inner & outer join are not binary operators. Each takes two tables & a condition that represents a function from a row to a boolean. So it does not make sense to talk about one being commutive or associative. For sort-of-commutative we could we could guess you mean (t1 join t2 on c) vs (t2 join t1 on c). But sort-of-associativity involves 3 tables & 2 conditions to rearrange. Using those terms just helps people ignore the important role of the conditions. Eg you limit your examples to certain conditions. Eg yet you should but don't limit your inner join paragraph the same way. – philipxy Apr 06 '19 at 22:20
@TylerCrompton: the assumption is wrong the order or inner joins matters much. This answer should amended. You can fail the order of inner joins by having two tables with unique foreign keys. See the answer below by Theo J. – Nir Pengas Dec 12 '19 at 18:14
Why these 2 joins returning different outputs : – S.P. Apr 16 '20 at 09:30
If order doesn't matter then why this query is wrong fo this probklem : https://www.hackerrank.com/challenges/full-score/problem : SELECT h.hacker_id, h.name FROM hackers h join challenges c on h.hacker_id = c.hacker_id join difficulty d on c.difficulty_level = d.difficulty_level join submissions s on s.hacker_id = h.hacker_id where s.score = d.score and c.difficulty_level = d.difficulty_level group by h.hacker_id, h.name having count(s.hacker_id) > 1 order by count(s.hacker_id) desc, h.hacker_id asc. If I reverse the order of joins I'm getting correct results. – S.P. Apr 16 '20 at 09:40
I can produce two SQL queries in my database. They are identical, except one joins tables in a different order. They return the same results, but the first runs in 1 second, the other in 14 seconds (even when I clear the query plan cache before running each), and get the same results if I run each query multiple times in a row – Developer Webs Apr 13 '22 at 15:33

Teo J. · Answer 2 · 2020-12-01T11:10:27.787

11

If you try joining C on a field from B before joining B, i.e.:

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, so in this case the order matters.

edited Dec 01 '20 at 11:10

answered May 09 '18 at 12:22

Teo J.

475
7
10

Yes this is right, the correct answer should be amended. – Nir Pengas Dec 12 '19 at 18:12

score 6 · Answer 3 · answered Mar 08 '12 at 09:52

6

for regular Joins, it doesn't. TableA join TableB will produce the same execution plan as 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 Mar 08 '12 at 09:52

Diego

34,802
21
91
134

6

This only addresses commutativity, but the examples in the question show that the asker is interested in associativity. ypercube's answer addresses both. – Mark Amery Dec 22 '14 at 22:50

score 3 · Answer 4 · answered Apr 26 '19 at 07:26

Oracle optimizer chooses join order of tables for inner join. Optimizer chooses the join order of tables only in simple FROM clauses . U can check the oracle documentation in their website. And for the left, right outer join the most voted answer is right. 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 into account: The size of each table The indexes available on each table Whether an index on a table is useful in a particular join order The number of rows and pages to be scanned for each table in each join order

I believe this is a good answer. It could be reworded to explain that the Oracle optimizer does it because it is indeed an equivalence in certain cases. There is another person who wrote something similar: https://mastel.org/blog/when-join-order-matters which explains that ordering is taken as hints for the optimizer but the optimizer may also reorder joins to improve performance. — Pierre-Luc Bertrand, Jun 16 '21 at 02:20

Does the join order matter in SQL?

4 Answers4

Linked

Related