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?

I'm not really sure what you're asking here. A `LEFT JOIN` means that you retain rows from the prior results, regardless of if a related row is found in the joined table or not. They are *both* `LEFT JOIN`ed, so both behave that way. — Thom A, Jul 25 '23 at 21:43
In this case it is a virtual table of all previous joins. `(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` The position of the ON clauses controls this https://dba.stackexchange.com/a/125423/3690 — Martin Smith, Jul 25 '23 at 21:45
@ThomA I know what left join does. I have multiple joins and want to know with which result set I am joining my t4, so I know what to expect, for debugging purposes — Hrvoje T, Jul 25 '23 at 21:49
Possible duplicate of https://stackoverflow.com/questions/36135436/multiple-left-joins-what-is-the-left-table — Ujjwal Manandhar, Jul 25 '23 at 22:22

score 2 · Accepted Answer · answered Jul 25 '23 at 22:21

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

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

1 Answers1