Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problemn with Right outer Join Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:28 AM
Points: 2, Visits: 32
All,
I am facing issue while using Right outer join in one of my queries (explained below using the temp table).Left outer join seems to return expected results while Right outer Join does not.

Create table #Temp(Column1 int, column2 int,column3 int)
GO
Insert into #Temp
Select 1,2,3
Union ALL
Select 2,3,4
Union ALL
Select 3,3,4
Union ALL
Select 4,3,4


Select * from #Temp
Select * from
(Select * from #temp) as t1 LEFT Outer Join
(Select * from #temp) as t2 on t1.Column1= t2.Column3

Select * from #Temp
Select * from
(Select * from #temp) as t1 RIGHT Outer Join
(Select * from #temp) as t2 on t1.Column1= t2.Column3

Below is what I get with Right outer Join.
3 3 4 1 2 3
4 3 4 2 3 4
4 3 4 3 3 4
4 3 4 4 3 4

which to my understanding is wrong as it should return NULLS where there is no match.
While for Left Outer Join below is the result.
1 2 3 NULL NULL NULL
2 3 4 NULL NULL NULL
3 3 4 1 2 3
4 3 4 2 3 4
4 3 4 3 3 4
4 3 4 4 3 4
Which seems to be right.

Any explanation on RIGHT Outer Join behaviour will be much appreciated.

Thanks,
Chhina
Post #1405070
Posted Wednesday, January 9, 2013 4:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:09 PM
Points: 6,251, Visits: 7,411
beerinders (1/9/2013)
All,
I am facing issue while using Right outer join in one of my queries (explained below using the temp table).Left outer join seems to return expected results while Right outer Join does not.



The results are dead accurate, you've just misplaced what you're expecting from the data.

In any outer join, you have this:

'all from here' -> 'Anything that might match here'.

When a row in the 'main' portion of the link has matches in the outer piece, you get the outer data. When it doesn't, you get NULLS for the outer data. This is working correctly. The part where your data looks 'off' is because your joins are not equivalent in returns from the outer component.

Since column 3 has 1 3 and 3 4's in it, Anything with a 4 in column 1 gets *3* rows returned from the outer component.

So, the left join piece doesn't find any connections for rows 1/2, a single join for 3, and *3* records for 4. So, 6 records returned.

The right join finds a single 3 in column 1 for the first record, and a single 4 in column 1 for the other 3 records, so four records returned.

This is returning exactly what it *should*, and hopefully that helps clear up why your results are different.

What are you hoping the Right Join to return that it's not? That may also allow us to clarify things for you.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1405075
Posted Thursday, January 10, 2013 8:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:28 AM
Points: 2, Visits: 32
Hi Kraig,
Thank you very much for your response. I guess I was confusing myself before. Actually I was looking at a query and was trying to understand the reasoning behind the use of the Right outer join and was getting some duplicate rows(which is logical after I understood the correct functioning).
Thanks again, much appreciated.

Chhina
Post #1405489
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse