Home » SQL & PL/SQL » SQL & PL/SQL » LEFT OUTER JOIN
LEFT OUTER JOIN [message #206814] Fri, 01 December 2006 11:25 Go to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
Hi

I am a ahving doubt on left outer join.
Here are two different codes which I am using.

Select T1.A, T2.B
from T1, T2

WHERE T1.ID = T2.ID(+) AND T2.Current_IND = 'Y'

Select T1.A, T2.B
from T1
LEFT OUTER JOIN T2 ON T1.ID = T2.ID AND T2.Current_ind = 'Y'

Do both perform same operation
Re: LEFT OUTER JOIN [message #206832 is a reply to message #206814] Fri, 01 December 2006 12:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
hm, I don't know the ansi syntax, so I don't know if the first one should be a left or a right outer join, but I do know that the first one is just an inner join, because of the T2.Current_IND = 'Y'.

[Updated on: Fri, 01 December 2006 12:40]

Report message to a moderator

Re: LEFT OUTER JOIN [message #206896 is a reply to message #206814] Sat, 02 December 2006 01:17 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

WHERE T1.ID = T2.ID(+) AND T2.Current_IND = 'Y'

This one is right outer join




Re: LEFT OUTER JOIN [message #206909 is a reply to message #206896] Sat, 02 December 2006 03:32 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Actually, it's the other way around...

WHERE T1.ID = T2.ID(+)

in ansi syntax is:
LEFT OUTER JOIN T2 ON T1.ID = T2.ID


Please be aware that the outer join in the "original" syntax is not complete, this should be:
WHERE T1.ID = T2.ID(+) 
AND T2.ID = 'Y'(+)


In ansi syntax you don't have to do this, the following works:
LEFT OUTER JOIN T2 ON T1.ID = T2.ID 
AND T2.CURRENT_IND='Y'





Previous Topic: Making logs - CREATE MATERIALIZED VIEW
Next Topic: How to get value from Exel sheet ang make a query in the Datebank
Goto Forum:
  


Current Time: Wed Dec 07 12:20:20 CST 2016

Total time taken to generate the page: 0.08588 seconds