Howie
Howie

Reputation: 2778

Multiple outer joins semantics

Some SQL code:

SELECT *
FROM table1 tab1 
   LEFT OUTER JOIN table2 tab2 ON (tab1.fg = tab2.fg)
   LEFT OUTER JOIN table4 tab4 ON (tab1.ss = tab4.ss)
   INNER JOIN table3 tab3 ON (tab4.xya = tab3.xya)
   LEFT OUTER JOIN table5 tab5 ON (tab4.kk = tab5.kk)

I know what different types of JOINs do, but what I'd like to know is: for each JOIN, which table assumes the role of the "LEFT" table? Will table1 always have the role of the "LEFT" table?

Upvotes: 12

Views: 25447

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

They are processed in top-to-bottom order, with the joins all associating to the "whole" of the prior FROM clause.

All things being equal:

  • tab1 is the mandatory partner for the OUTER JOIN with the optional partner tab2
  • the above is the mandatory partner for the OUTER JOIN with the optional partner tab4
  • the above and tab4 are both mandatory partners in the INNER JOIN
  • the above is the mandatory partner for the OUTER JOIN with the optional partner tab5

However, the problem with this query

SELECT *
FROM table1 tab1 
LEFT OUTER JOIN table2 tab2 ON tab1.fg = tab2.fg
LEFT OUTER JOIN table4 tab4 ON tab1.ss = tab4.ss
INNER JOIN table3 tab3 ON tab4.xya = tab3.xya
LEFT OUTER JOIN table5 tab5 ON tab4.kk = tab5.kk

Is that the INNER JOIN with table3 uses a condition that REQUIRES tab4 to get involved, making it virtually a mandatory link to retain records from the left part, so in total tab1/tab4/tab3 have to successfully join, with tab2 and tab5 optional.

Upvotes: 16

Related Questions