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