SQL - The difference between these joins?

Question

I should probably know about this, but what if there is a difference between the two instructions below?

Nested Connection:

SELECT t1.* FROM table1 t1 INNER JOIN table2 t2 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID ON t2.table2_ID = t1.table1_ID 

More traditional mix:

 SELECT t1.* FROM table1 t1 INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID 

Answer 1

Well, this is the order of operations.

 SELECT t1.* FROM table1 t1 INNER JOIN table2 t2 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID ON t2.table2_ID = t1.table1_ID 

can be rewritten as:

 SELECT t1.* FROM table1 t1 -- inner join t1 INNER JOIN (table2 t2 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID) -- with this ON t2.table2_ID = t1.table1_ID -- on this condition 

So, first you LEFT JOIN t2 with t3, based on the join condition: table3_ID = table2_ID, then you INNER JOIN t1 with t2 on table2_ID = table1_ID.

In your second example, you first INNER JOIN t1 with t2, and then LEFT JOIN the resulting internal join to table t3 in the condition table2_ID = table1_ID.

 SELECT t1.* FROM table1 t1 INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID 

can be rewritten as:

 SELECT t1.* FROM (table1 t1 INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID) -- first inner join LEFT JOIN -- then left join table3 t3 ON t3.table3_ID = t2.table2_ID -- the result with this 

EDIT

I apologize. My first comment was wrong. These two queries will give the same results, but there may be a difference in performance, since the first query can run slower than the second query in some cases (when table 1 contains only a subset of the elements in table 2), because the LEFT JOIN will be executed first - and only then intersects with table 1. Unlike the second query, which allows the query optimizer to do this work.

Answer 2

In your specific example, I do not think that there should be any difference in the query plans, but a certain difference in readability. Your second example is much simpler.

If you were to change the types of joins in the example, you could get very different results.

 SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON t2.table2_ID = t1.table1_ID INNER JOIN table3 t3 ON t3.table3_ID = t2.table2_ID -- may not produce the same results as... SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 INNER JOIN table3 t3 ON t3.table3_ID = t2.table2_ID ON t2.table2_ID = t1.table1_ID 

Based on the fact that the order of joins matters in many cases - careful consideration should be given to how you write your join syntax. If you find that the second example is what you are really trying to execute, I would consider rewriting the request so that you can pay more attention to the order of your joins ...

 SELECT t1.* FROM table2 t2 INNER JOIN table3 t3 ON t3.table3_ID = t2.table2_ID RIGHT JOIN table1 t1 ON t2.table2_ID = t1.table1_ID 

Answer 3

The best way to see what is different in these two queries is to compare the Query Plan for both of these queries.

There is no difference in the result sets for these IFs ; there are always rows in table3 for a given row in table2.

I tried this in my database, and the difference in query plans was that 1. For the first query, the optimizer decided to first make a connection in table 2 and in table 3. 2. For the second query, the optimizer decided to join table1 and table2 first.

Answer 4

You should not see any difference between the two queries if your DBMS optimizer is down to zero. This, however, even for high-yielding high-performance platforms, is not an assumption that I would be sure to create one, so I would be completely not surprised to find out that the query plans (and therefore the execution time) varied.