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   -- A select   from   a left join b            on  lt blahblah gt         left join c            on  lt blahblan gt    -- B select   from   a left join c            on  lt blahblah gt         left join b            on  lt blahblan gt     -- C select   from   a join b            on  lt blahblah gt         join c            on  lt blahblan gt    -- D select   from   a join c            on  lt blahblah gt         join b            on  lt blahblan gt

User · Answer

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.

User · Answer

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  lt   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

User · Answer

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

User · Answer

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

[sql] Does the join order matter in SQL?

Examples related to sql

Examples related to join

Examples related to relational-database