12

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?

Howie
  • 2,760
  • 6
  • 32
  • 60
  • 1
    [Here is a good visual explanation of joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) – Taryn Oct 10 '12 at 11:30
  • 2
    Looks cool, but I'm still not sure what are the semantics in case of multiple joins. – Howie Oct 10 '12 at 11:40

1 Answers1

16

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.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262