Home » SQL & PL/SQL » SQL & PL/SQL » Left outer join Vs. Right outer join (merged)
Left outer join Vs. Right outer join (merged) [message #418367] Fri, 14 August 2009 07:25 Go to next message
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 Go to previous messageGo to next message
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 #453828 is a reply to message #418367] Sun, 02 May 2010 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one is safer, or more performant, or anything you think of, they are the same ones, that is "A left outer join B" is equivalent to "B right outer join A".

Regards
Michel
Re: Left outer join Vs. Right outer join (merged) [message #453836 is a reply to message #418367] Sun, 02 May 2010 03:05 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

is there any recommendations to use any join?

Depends your requirement.

Michel has already given you the answer to your questions.


Here a few more links which may be helpful for you. Please go through it.


Joins
Examples:
http://psoug.org/reference/joins.html
http://www.techonthenet.com/sql/joins.php

Thanks
Ved
Re: Left outer join Vs. Right outer join (merged) [message #453878 is a reply to message #418367] Sun, 02 May 2010 17:26 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Left outer join Vs. Right outer join (merged) [message #453908 is a reply to message #418367] Mon, 03 May 2010 01:18 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
old habits die hard. Thanks for being so polite.

I recognize that ANSI syntax is in at least a few ways, superior to the old school sql. Of particular note it is often less error prone. I should really take the time to learn it.

Kevin
Previous Topic: sys_refcursor not returning values with datatype clob (size >4000)
Next Topic: Updating a File with UTL_FILE
Goto Forum:
  


Current Time: Sat Dec 03 22:28:02 CST 2016

Total time taken to generate the page: 0.08016 seconds