Triple Join Help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Triple Join Help!

Author

Topic

cyclogenisis
Starting Member

2 Posts

Posted - 2014-05-21 : 22:10:44

Okay I'm having issues conceptualizing how triple joins work when you use outer.

E.g. FROM table1 LEFT OUTER JOIN table2 on table1.col1 = table2.col2
RIGHT OUTER JOIN table3.col4 = table2.col1

Can someone explain to me what rows would be expected to be returned from each table? For instance, if there are nulls for the columns we are joining in table 2 and table 1 how do we know which table will be forced to show all their rows? I am awfully confused.

Appreciate the help.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-05-22 : 08:44:58

http://www.w3schools.com/SQl/sql_join.asp

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

cyclogenisis
Starting Member

2 Posts

Posted - 2014-05-22 : 19:10:09

can someone enlighten me where the query in the first post would land (area #)?

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-05-23 : 01:55:16

I used the below query as an example .

select * from Person.Person a
left outer join [HumanResources].[Employee] b
on a.BusinessEntityID = b.BusinessEntityID
right outer join [HumanResources].[EmployeeDepartmentHistory] c
on c.BusinessEntityID = b.BusinessEntityID

it uses the output of the below query to "right outer join" EmployeeDepartmentHistory

select * from Person.Person a
left outer join [HumanResources].[Employee] b
on a.BusinessEntityID = b.BusinessEntityID

Lets say person table has 19k records , employee table has also 19k but EmployeeDepartmentHistory has 296 records.

If i am doing a left outer join of Person & Employee i will get 19k records and if i do a right outer join with EmployeeDepartmentHistory to that output , i will get only 296 records.

Hema Sunder

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-05-23 : 01:56:55

So i see it as intersection of the two circles and whole of 3rd circle , basically the output is 3,4,6 and 7

Hema Sunder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources