Which table is left table in left join on multi-join?

Question

SELECT *
FROM t1
JOIN t2 ON t2.id = t1.t2_id
LEFT JOIN t3 ON t3.id = t2.t3_id
LEFT JOIN t4 ON t4.id = t1.t4_id

Which is a left table when left joining t4? Is it previous result set - combination od previous joins, or a table before, which is t3, or is it t1 because of ON clause?

Answer 1

In this case you can consider the LEFT table in the join against t4 to be

SELECT *
FROM   t1
       INNER JOIN t2
               ON t2.id = t1.t2_id
       LEFT JOIN t3
              ON t3.id = t2.t3_id

This is the typical case where every JOIN clause has an ON clause immediately afterwards.

It is possible to not do that though.

As an example for the query

SELECT *
FROM   t1
       LEFT JOIN t2
              ON t2.id = t1.t2_id
       INNER JOIN t3
                  LEFT JOIN t4
                         ON t4.id = t3.t4_id
               ON t3.id = t1.t3_id 

Then logically

• t1 is left joined to t2 - call that t1_t2
• t3 is left joined to t4 - call that t3_t4
• t1_t2 is inner joined to t3_t4

The placement of the ON clause controls this