What is the difference between specifying 'ON' at a join and after all the joins?

Question

I came across a code which joins two tables and on specifies the link (the 'ON' clause) between the three tables at the end.

The code was written like this:

SELECT * 
FROM tb1 AS a  
LEFT OUTER JOIN tbl2 AS b  
LEFT OUTER JOIN tbl3 AS c 
ON b.ColA = c.ColA ON a.ColA = b.ColA

The result of this was different from when I changed it to:

SELECT *
FROM tb1 AS a  
LEFT OUTER JOIN tbl2 AS b ON a.ColA = b.ColA
LEFT OUTER JOIN tbl3 AS c ON b.ColA = c.ColA 

Could someone please explain the difference between these two joins?

Answer 1

Please note that the database is free to decide in which order to process the tables. So especially oracle will change the query to its liking upon its internal statistics. Ofcourse only in a way that leads logically to the same results.

Putting the on clause directly to the tables to be joined makes it more readle.

So my recommendation is to do tb1 left out join tbl23 on tb1.xxx=tb2.yyy

Answer 2

I am surprised that the two queries yield different results, as they look logically equivalent to me. As another answer has pointed out, this is logically equivalent to the following query (but without duplicate column errors):

SELECT  * 
FROM    tb1 AS a  
        LEFT OUTER JOIN 
        (   SELECT  *
            FROM    tbl2 AS b  
                    LEFT OUTER JOIN tbl3 AS c 
                        ON b.ColA = c.ColA
        ) AS b
            ON a.ColA = b.ColA

i.e. it changes the order of operations, tbl2 is joined to tbl3, first, then the result of that query is joined with tb1. This is typically more usefull when you need to INNER JOIN on a table that is already part of an OUTER JOIN. For example, if you only wanted to return records from tbl2 where the corresponding entry was in tbl3, you couldn't change the original query to this:

SELECT *
FROM tb1 AS a  
LEFT OUTER JOIN tbl2 AS b ON a.ColA = b.ColA
INNER JOIN tbl3 AS c ON b.ColA = c.ColA 

As this would also remove any records from tb1 with no corresponding entry in tbl2, whereas if you wrote it like this:

SELECT * 
FROM tb1 AS a  
LEFT OUTER JOIN tbl2 AS b  
INNER JOIN tbl3 AS c 
ON b.ColA = c.ColA ON a.ColA = b.ColA

This is where the difference is more noticable. The latter is equivalent to this:

SELECT  * 
FROM    tb1 AS a  
        LEFT OUTER JOIN 
        (   SELECT  *
            FROM    tbl2 AS b  
                    INNER JOIN tbl3 AS c 
                        ON b.ColA = c.ColA
        ) AS b
            ON a.ColA = b.ColA

Which would correctly remove any records from tbl2 where there was no corresponding record in tbl3, but retain records from tbl1 that have no corresponding record in the resulting dataset of tbl2 and tbl3.

SAMPLE SCHEMA AND QUERIES

DECLARE @T1 TABLE (ColA INT);
DECLARE @T2 TABLE (ColA INT);
DECLARE @T3 TABLE (ColA INT);
INSERT @T1 (ColA) VALUES (1), (2), (3);
INSERT @T2 (ColA) VALUES (1), (2);
INSERT @T3 (ColA) VALUES (1);


-- query 1
SELECT *
FROM @T1 AS a  
LEFT OUTER JOIN @T2 AS b ON a.ColA = b.ColA
LEFT OUTER JOIN @T3 AS c ON b.ColA = c.ColA;

-- query 2
SELECT * 
FROM @T1 AS a  
LEFT OUTER JOIN @T2 AS b  
LEFT OUTER JOIN @T3 AS c 
ON b.ColA = c.ColA ON a.ColA = b.ColA;

-- query 3
SELECT *
FROM @T1 AS a  
LEFT OUTER JOIN @T2 AS b ON a.ColA = b.ColA
INNER JOIN @T3 AS c ON b.ColA = c.ColA;

-- query 4
SELECT * 
FROM @T1 AS a  
LEFT OUTER JOIN @T2 AS b  
INNER JOIN @T3 AS c 
ON b.ColA = c.ColA ON a.ColA = b.ColA

RESULTS

-- QUERY 1 & 2
T1.ColA     T2.ColA     T3.ColA
----------------------------------
1           1           1
2           2           NULL
3           NULL        NULL

-- QUERY 3
T1.ColA     T2.ColA     T3.ColA
----------------------------------
1           1           1

-- QUERY 4
T1.ColA     T2.ColA     T3.ColA
----------------------------------
1           1           1
2           NULL        NULL
3           NULL        NULL

Answer 3

Your first query is evaluated like the following

SELECT * 
FROM tb1 AS a  
LEFT OUTER JOIN 
(
  SELECT * 
  FROM tbl2 AS b  
  LEFT OUTER JOIN tbl3 AS c 
  ON b.ColA = c.ColA
) AS temp 
ON a.ColA = temp.ColA

Which is not logically same as your second query. That's why, you are getting different result.

If you used INNER JOIN instead of LEFT OUTER JOIN you would get same result for both queries.

N.B: the above query will give you error because tbl2 and tbl3 both have a column with same name. so you may need to alias that to execute the above query properly.