Re: Left outer join

Andy wrote:

>I realise that this has probably been done to death but I am having
>trouble with my outer join statement. It doesn't return more rows
>that that of an equal join.
>
>Here's a simple test I set up:
>
>Table AR1
>select * from ar1
>
>ACCOUNT
>100
> 999
>
>Table AR2
>select * from ar2;
>
>ACCOUNT ATTVALUE ATT
>100 A COSTC C1
>999 A PROJECT 40
>
>Outer Join Statement
>select *
>from ar1 ac, ar2 cc
>where ac.account = cc.account(+)
>and cc.att= 'C1';
>
>ACCOUNT ACCOUNT ATTVALUE ATT
>100 100 A COSTC C1
>
>My understanding is that it should return all the rows from the first
>table in this example ar1 and null values where there aren't a
>matching. so some thing like this:
>
>ACCOUNT ACCOUNT ATTVALUE ATT
>100 100 A COSTC C1
>999 NULL NULL NULL
>
>
>Any help greatly appreciated.
>
>Thanks
>Andy
>
>

Your understanding of what an outer join should do is correct. But I can't tell from what
you have posted what it is you are expecting that is different from what you are retrieving.

Try these two statements and maybe it will become clearer what is happening:

select *
from ar1 ac, ar2 cc
where ac.account = cc.account(+);

select *
from ar1 ac, ar2 cc
where cc.account = ac.account(+);

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)