Left outer join Vs. Right outer join (merged) [message #418367] |
Fri, 14 August 2009 07:25  |
veerumuppalla
Messages: 1 Registered: August 2009
|
Junior Member |
|
|
Hi all,
I am beginner in SQL and just want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?
Thanks in advance
-Veeru
|
|
|
Re: Left outer join Vs. Right outer join (merged) [message #453827 is a reply to message #418367] |
Sun, 02 May 2010 01:09   |
sibanjan
Messages: 3 Registered: May 2010 Location: pune,india
|
Junior Member |
|
|
In left outer join, the result of the join of two tables say A and B always contain all the records of A even if there is no matching records found in B i.e. the record in B is null.
In right outer join , the reult of the join of two tables A and B always contain all the records of B even if there is no matching records found in A i.e the record in A is null.
|
|
|
|
|
Re: Left outer join Vs. Right outer join (merged) [message #453878 is a reply to message #418367] |
Sun, 02 May 2010 17:26   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
There is no difference. It is stupid terminology invented to confuse people.
Tell me the difference between these two queries and why the terminology is relevant.
Right Outer Join
select *
from t1,t2
where t1.c1 = t2.c1(+)
/
Left Outer Join
select *
from t1,t2
where t2.c1(+) = t1.c1
/
Notice these are the same query. The only difference between them is the order of columns in the WHERE predicate joining the tables. Calling one left outer join and the other right outer join provides absolutely no value to any discussion and is just stupid. Indeed putting a name to it suggests there is some intelligence to it and that it makes some kind of difference. To this end it is a bad concept and I for one have always been anoyed by these terms.
They are bad terminology and should never be used. It is just an outer join, nothing else.
In comparison, consider the phrase FULL OUTER JOIN. This has meaning because it describes something different from just plain old JOIN.
Kevin
[Updated on: Sun, 02 May 2010 18:08] Report message to a moderator
|
|
|
Re: Left outer join Vs. Right outer join (merged) [message #453882 is a reply to message #453878] |
Sun, 02 May 2010 23:40   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I don't entirely agree - there is a difference. Or if not strictly a difference, then a need for ROJs. Consider the following:
SELECT /*+ ORDERED */ *
FROM t1
LEFT OUTER JOIN t2 ON t1.ky = t2.key
SELECT /*+ ORDERED */ *
FROM t2
RIGHT OUTER JOIN t1 ON t1.ky = t2.key
These are functionally the same (i.e. same result) but the execution plan in the second example cannot be easily enforced without using the RIGHT OUTER JOIN syntax.
I know Kevin will disagree because he has a nostalgic love of the (+) syntax. And that's fair enough - I don't condemn him for it - or indeed any other subjective preference others may have.
But my subjective preference is to use ANSI join syntax, and that means on rare occasions I may be forced to employ the RIGHT OUTER JOIN syntax - which I concede is difficult to read - when I need to instruct the CBO with an ORDERED hint.
Ross Leishman
|
|
|
|