January 9, 2013 at 4:27 pm
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.
334123
434234
434334
434434
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.
123NULLNULLNULL
234NULLNULLNULL
334123
434234
434334
434434
Which seems to be right.
Any explanation on RIGHT Outer Join behaviour will be much appreciated.
Thanks,
Chhina
January 9, 2013 at 4:40 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 10, 2013 at 8:22 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply