Re: Outer join by (+)

On Dec 17, 9:01 am, nova1..._at_gmail.com wrote:
> There are two type to write select qurey left or right outer join
>
> 1.
> from table1 left outer join table2 on (table1..column1=table2.column1)
>
> 2.
> from table1, table2
> where table1..column1=table2.column1(+)
>
> Now I will add constant condation like table2.column2='AAAA'
>
> it will be like this
>
> 1.
> from table1 left outer join table2 on (table1..column1=table2.column1
> and table2.column2='AAAA')
>
> 2.
> from table1, table2
> where table1..column1=table2.column1(+)
> and table2.column2='AAAA'
>
> but this is not left outer join becasue there is condition.
>
> Question:
> - How can I add this condition in secound case and still left outer
> join?

I believe you'll have to do

 from table1, table2
where table1.column1 = table2.column1(+)   and table2.column2(+) = 'AAAA'

> - How can I make full outer join in secound case?

If I'm reading the docs of 10g properly you cannot do that with the old syntax:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054062

I'd generally prefer the OUTER JOIN syntax as it is more clear and easier to grasp. Also, it has higher expressiveness. See here for explanation: http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html

Kind regards

robert Received on Mon Dec 17 2007 - 03:41:41 CST