4

Can we achieve desired results of joining tables by executing joins in whatever order? Suppose we want to left join two tables A and B (order AB). We can get the same results with right join of B and A (BA).

What about 3 tables ABC. Can we get whatever results by only changing order and joins types? For example A left join B inner join C. Can we get it with BAC order? What about if we have 4 or more tables?

Update. The question Does the join order matter in SQL? is about inner join type. Agreed that then the order of join doesn't matter. The answer provided in that question does not answer my question whether it is possible to get desired results of joining tables with whatever original join types (join types here) by choosing whatever order of tables we like, and achieve this goal only by manipulating with join types.

Community
  • 1
  • 1
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • Not sure what you are trying to accomplish. More often than not, the logical easier to understand approach works. Even breaking things into CTE's or subqueries for the sake of brevity is more advisable than creating the "perfect" query. Start from "one" point and use the joins that make sense. – Anthony Horne Feb 27 '15 at 08:33
  • 1
    It is possible to change the order and obtain the same result. And you can use `(` and `)` to remove ambiguity. – Salman A Feb 27 '15 at 08:55
  • 1
    "A left join B inner join C" is the same as "B right join A inner join C". Most people find left join easier to understand. – jarlh Feb 27 '15 at 09:16
  • @Salman is it necessary to use brackets? Is it possible to achieve goal (change table order) without using brackets, by juggling with join types only? – Przemyslaw Remin Feb 27 '15 at 09:50
  • @PrzemyslawRemin see http://stackoverflow.com/questions/9614922/does-the-join-order-matter-in-sql – Salman A Feb 27 '15 at 10:43

1 Answers1

2

In an inner join, the ordering of the tables in the join doesn't matter - the same rows will make up the result set regardless of the order they are in the join statement.

In either a left or right outer join, the order DOES matter. In A left join B, your result set will contain one row for every record in table A, irrespective of whether there is a matching row in table B. If there are non matching rows, this is likely to be a different result set to B left join A.

In a full outer join, the order again doesn't matter - rows will be produced for each row in each joined table no matter what their order.

Regarding A left join B vs B right join A - these will produce the same results. In simple cases with 2 tables, swapping the tables and changing the direction of the outer join will result in the same result set.

This will also apply to 3 or more tables if all of the outer joins are in the same direction - A left join B left join C will give the same set of results as C right join B right join A.

If you start mixing left and right joins, then you will need to start being more careful. There will almost always be a way to make an equivalent query with re-ordered tables, but at that point sub-queries or bracketing off expressions might be the best way to clarify what you are doing.

As another commenter states, using whatever makes your purpose most clear is usually the best option. The ordering of the tables in your query should make little or no difference performance wise, as the query optimiser should work this out (although the only way to be sure of this would be to check the execution plans for each option with your own queries and data).

EmbraCraig
  • 56
  • 1
  • 6