SQL Inner Join Order

Question

If I have a table A with primary key w, and table B with primary key x and foreign key referring to A.w called y, and a field z. What is the difference between the following queries:

SELECT w, x, y, z FROM A INNER JOIN B ON A.w = B.y WHERE B.z = SomeValue

and

SELECT w, x, y, z FROM B INNER JOIN A ON A.w = B.y WHERE B.z = SomeValue

Is there still a difference if B.z and B.y are a compound primary key?

If there isn't a difference which is 'more standard' code?

There is no difference between the two queries. The order of operands for an `inner join` does not affect the results and should not affect the execution plan. — Gordon Linoff, Mar 10 '17 at 11:42
To find out _if_ that has any effect, check the execution plan. — , Mar 10 '17 at 11:51
@a_horse_with_no_name The duplciate is wrong, the question is about order of arguments of 1 join (commutativity) but the duplicate is about order of multiple joins (associativity). (I'm looking for an appropriate duplicate, please let me know of you have one. Oldest so far is only 2013 https://stackoverflow.com/q/19612208/3404097. ) — philipxy, Apr 13 '20 at 22:41

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

There is no difference between the two given queries. For INNER joins, no, the order doesn't matter. For (LEFT, RIGHT or FULL) OUTER joins, yes, the order matters.

Please see this thread for full detail.

edited May 23 '17 at 12:31

Community

1
1

answered Mar 10 '17 at 11:43

Muhammad Qasim

1,622
14
26

Would either of them be better programming style? – euanjt Mar 10 '17 at 11:44
Please see my updated answer. :) – Muhammad Qasim Mar 10 '17 at 11:46

SQL Inner Join Order

1 Answers1