Inner Join vs. Left Outer Join

Inner Join vs. Left Outer Join

Post by SnVzdGluIE » Sat, 21 Mar 2009 07:41:01


Hi.

I am not sure how the Inner Join & Left Outer Join work when many tables get
involved.

I know the basic concept that the Inner Join only retrieves what is common
on both tables on the left and right.
I know the basic concept that Left Outer Join retrieves all data on the Left
table and only what the common on the right table.
But I am not sure what exactly happens when there are so many tables involved.

Here is an example.

SELECT ...
FROM
tblEmployee AS e WITH LEFT OUTER JOIN
tblCurrentManager AS eh ON e.sEmpNum = eh.EmpNum LEFT OUTER JOIN
tblEmpId AS b ON e.sEmpNum = b.PERSON_NUMBER LEFT OUTER JOIN
tblTrans2 AS c ON e.sEmpNum = c.PERSON_NUMBER

Are we retrieving most of data starting from the 1st one (tblEmployee and
goes on..) to the last, right?

What about this then?
SELECT ...
FROM
tblEmployee AS e INNER JOIN
tblCostCenter AS c ON e.sCCNum = c.sCCNum LEFT OUTER JOIN
tblCurrentManager AS eh ON e.sEmpNum = eh.EmpNum LEFT OUTER JOIN
tblEmployee AS e2 ON eh.MgrEmpNum = e2.sEmpNum

Are we trying to show only the common data between tblEmployee &
tblCostCenter, but what is happening when we do
"tblCostCenter AS c ON e.sCCNum = c.sCCNum LEFT OUTER JOIN
tblCurrentManager"?
It is bringing most of data from tblCostCenter, isn't it?

Thanks so much for any feedback.
Justin
 
 
 

Inner Join vs. Left Outer Join

Post by Erland Som » Sat, 21 Mar 2009 08:32:02

Justin Doh ( XXXX@XXXXX.COM ) writes:

A good start to rewrite in a more readable way. (So that I can read it!)

FROM tblEmployee AS e
LEFT JOIN tblCurrentManager AS eh ON e.sEmpNum = eh.EmpNum
LEFT JOIN tblEmpId AS b ON e.sEmpNum = b.PERSON_NUMBER
LEFT JOIN tblTrans2 AS c ON e.sEmpNum = c.PERSON_NUMBER


We get all rows from tblEmployee and then we add rows from the other
tables.

One thing to watch out for here. Say that an employee matches two rows
in tblEmpID and 20 rows in tblTrans2. This adds 40 rows to the result
set, as you have a cartesian product between the two tables.

But as long as at most one table can bring in more than one employee,
there is no issue.


SELECT ...
FROM tblEmployee AS e
JOIN tblCostCenter AS c ON e.sCCNum = c.sCCNum
LEFT JOIN tblCurrentManager AS eh ON e.sEmpNum = eh.EmpNum
LEFT JOIN tblEmployee AS e2 ON eh.MgrEmpNum = e2.sEmpNum

If an employee does not have a cost centre, he will be excluded from
the query. But supposedly, tblEmployee.CCNum is not nullable and there
is an FK constraint, in which case it does not matter.

The left join tbl tblCurrentManager is probably a left join because
some employees does not have a manager, for instance the managing
director.

Next join is also a left join, because if it was an inner join we would
now lose the managing director from the result set. However, this is
a better way to write it:

LEFT JOIN (tblCurrentManager AS eh
JOIN tblEmployee AS e2 ON eh.MgrEmpNum = e2.sEmpNum)
ON e.sEmpNum = eh.EmpNum

Now we say that eh and e2 should be inner-joined to each other first,
and then we perform the left-join.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Links for SQL Server Books Online:
SQL 2008: http://www.yqcomputer.com/
SQL 2005: http://www.yqcomputer.com/
SQL 2000: http://www.yqcomputer.com/

 
 
 

Inner Join vs. Left Outer Join

Post by Dan Guzma » Sat, 21 Mar 2009 20:13:45

Here's an excellent diagram that shows the logical order of query
processing:
http://www.yqcomputer.com/ %20Query%20Processing%20Poster.pdf

This is described detail in in Itzik's Microsoft SQL Server 2008: T-SQL
Querying book, which I strongly recommend. I haven't had a chance to read
the T-SQL Fundamentals book but I've heard that is very good too. That book
is more of a beginner level read.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://www.yqcomputer.com/
 
 
 

Inner Join vs. Left Outer Join

Post by alkuz » Sat, 21 Mar 2009 23:01:22

On Mar 19, 5:41m, Justin Doh < XXXX@XXXXX.COM >

> FROM gt; > tblEmployee AS e WITH LEFT OUTER JO>N
> tblCurrentManager AS eh ON e.sEmpNum = eh.EmpNum LEFT OUTER JO>N
> tblEmpId AS b ON e.sEmpNum = b.PERSON_NUMBER LEFT OUTER JO>N
> tblTrans2 AS c ON e.sEmpNum = c.PERSON_NUMB>R >>
> Are we retrieving most of data starting from the 1st one (tblEmployee a>d
> goes on..) to the last, righ>? >>
> What about this the>?
> SELECT .>.
> FROM gt; > tblEmployee AS e INNE> JOIN
> tblCostCenter AS c ON e.sCCNum = c.sCCNum LEFT OUTE> JOIN
> tblCurrentManager AS eh ON e.sEmpNum = eh.EmpNum LEFT OUTE> JOIN
> tblEmployee AS e2 ON eh.MgrEmpNum = e2.s>mp>um
>
> Are we trying to show only the common data between tblEmpl>yee &
> tblCostCenter, but what is happening when>we do
> "tblCostCenter AS c ON e.sCCNum = c.sCCNum LEFT OUTE> JOIN
> tblCurrentMan>ger"?
> It is bringing most of data from tblCostCenter, isn>t >t?
>
> Thanks so much for any fee>back.
> Justin

good explanation:

http://www.yqcomputer.com/
 
 
 

Inner Join vs. Left Outer Join

Post by SnVzdGluIE » Thu, 26 Mar 2009 03:37:01

rland,

Thank you so much for your help.
Justin

"Erland Sommarskog" wrote: