Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Left outer join
On Wed, 12 Nov 2003 09:03:28 -0800, Daniel Morgan <damorgan_at_x.washington.edu> 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
In order to get that result you would have to apply the (+) operator to the
cc.att = 'C1' condition as well.
Whilst you have an outer join with respects to the 'account' field join, you then have a filter on cc.att = 'C1' which eliminates the additional row that the outer join added (i.e. where cc.att is null).
SQL> select *
2 from ar1 ac, ar2 cc
3 where ac.account = cc.account (+)
4 and cc.att = 'C1';
ACCOUNT ACCOUNT ATTVALUE AT
---------- ---------- ------------ --
100 100 A COSTC C1 [explain plan]:
| Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | |* 1 | FILTER | | | | | | 2 | NESTED LOOPS OUTER | | | | | | 3 | TABLE ACCESS FULL | AR1 | | | | | 4 | TABLE ACCESS BY INDEX ROWID| AR2 | | | | |* 5 | INDEX RANGE SCAN | AR2_PK | | | | -----------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter("CC"."ATT"='C1')
5 - access("AC"."ACCOUNT"="CC"."ACCOUNT"(+))
Note the filter (1).
Whereas with (+) applied throughout:
SQL> select *
2 from ar1 ac, ar2 cc
3 where cc.account (+) = ac.account
4 and cc.att (+) = 'C1';
ACCOUNT ACCOUNT ATTVALUE AT
---------- ---------- ------------ --
100 100 A COSTC C1 999
[explain plan]:
| Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | NESTED LOOPS OUTER | | | | | | 2 | TABLE ACCESS FULL | AR1 | | | | | 3 | TABLE ACCESS BY INDEX ROWID| AR2 | | | | |* 4 | INDEX UNIQUE SCAN | AR2_PK | | | | ----------------------------------------------------------------------------
Predicate Information (identified by operation id):
4 - access("CC"."ACCOUNT"(+)="AC"."ACCOUNT" AND "CC"."ATT"(+)='C1')
See the SQL Reference, section on Joins in the "Queries and Subqueries" chapter.
-- Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk) Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)Received on Wed Nov 12 2003 - 15:32:15 CST
![]() |
![]() |