SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problemn with Right outer Join


Problemn with Right outer Join

Author
Message
beerinders
beerinders
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 55
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
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8573 Visits: 7660
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
beerinders
beerinders
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 55
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search