InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 03:21:51 GMT20http://www.sqlservercentral.com/Forums/Topic1405070-392-1.aspxHi 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.ChhinaThu, 10 Jan 2013 08:22:23 GMTbeerindershttp://www.sqlservercentral.com/Forums/Topic1405070-392-1.aspx[quote][b]beerinders (1/9/2013)[/b][hr]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.[/quote]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.Wed, 09 Jan 2013 16:40:16 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1405070-392-1.aspxAll,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)GOInsert into #TempSelect 1,2,3Union ALLSelect 2,3,4Union ALLSelect 3,3,4Union ALLSelect 4,3,4Select * from #TempSelect * from (Select * from #temp) as t1 LEFT Outer Join (Select * from #temp) as t2 on t1.Column1= t2.Column3Select * from #TempSelect * from (Select * from #temp) as t1 RIGHT Outer Join (Select * from #temp) as t2 on t1.Column1= t2.Column3Below is what I get with Right outer Join.3 3 4 1 2 34 3 4 2 3 44 3 4 3 3 44 3 4 4 3 4which 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 NULL2 3 4 NULL NULL NULL3 3 4 1 2 34 3 4 2 3 44 3 4 3 3 44 3 4 4 3 4Which seems to be right.Any explanation on RIGHT Outer Join behaviour will be much appreciated.Thanks,ChhinaWed, 09 Jan 2013 16:27:11 GMTbeerinders