Re: Left outer join

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 12 Nov 2003 21:32:15 +0000
Message-ID: <fp85rvokmd3qs7m8s1smtru1l05jenvm5j@4ax.com>

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