Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 675
Howie Avatar asked Oct 10 '12 11:10

Howie


People also ask

What are the different types of SQL outer joins?

We use the SQL OUTER JOIN to match rows between tables. We might want to get match rows along with unmatched rows as well from one or both of the tables. We have the following three types of SQL OUTER JOINS. SQL Full Outer Join SQL Left Outer Join SQL Right Outer Join Let’s explore each of SQL Outer Join with examples. SQL Full Outer Join

What is the difference between RIGHT OUTER JOIN and outer join?

Right Outer Join : The right join operation returns all record from right table and matching records from the left table. On a matching element not found in left table, NULL is represented in that case. 3. Full Outer Join : The full outer Join keyword returns all records when there is a match in left or right table records.

What is the concept of multiple joins?

Here we are going to implement the concept of multiple joins. Multiple joins can be described as a query containing joins of the same or different types used more than once, thus giving them the ability to combine multiple tables.

What is LEFT OUTER JOIN in SQL?

SQL LEFT OUTER JOIN 1 It gives the output of the matching row between both the tables 2 If no records match from the left table, it also shows those records with NULL values More ...


1 Answers

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.

like image 133
RichardTheKiwi Avatar answered Oct 17 '22 18:10

RichardTheKiwi