Re: Left outer join

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

On 12 Nov 2003 08:25:39 -0800, andyrich_1_at_hotmail.com (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
 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:33:15 CST