Re: outer join

Michiel Perdeck wrote:
>
> Can anyone explain to me in a few sentences where exactly to place the
> (+) of an outer join in Oracle?
>
> Take the following situation:
>
> Ther is a maintable MAIN, containing an id main_id and foreign keys to
> secundairy tables, e.g. MAIN.sub_id points to SUBTABLE which contains
> the field SUBTABLE.text.
> If I want to print all the records of MAIN with their corresponding
> texts, I could write:
>
> SELECT m.main.id, s.text
> FROM MAIN m, SUBTABLE s
> WHERE m.sub_id = s.sub_id
>
> But this will only show those records where m.sub_id is not NULL. If I
> want to show all records, should I write
> WHERE m.sub_id (+) = s.sub_id
> or
> WHERE m.sub_id = s.sub_id (+)?
>
> I think the latter. But why exactly?
> Would it be a useable rule-of-thumb to state that in cases like this,
> the (+) must be placed next to the Primary key?
>
> Thanks for any help,
> Michiel

Think of it this way. Put the (+) on the column which appears in the table that may not have a matching row. You are adding (hence the (+)) a row to that table so it can match. Here is your example both ways:

SELECT m.main.id, s.text
  FROM MAIN m, SUBTABLE s
 WHERE m.sub_id = s.sub_id(+)

The above statement would return all rows from MAIN matched with corresponding rows in SUBTABLE and if there was no sub_id in SUBTABLE to match it would return a null row.

SELECT m.main.id, s.text
  FROM MAIN m, SUBTABLE s
 WHERE m.sub_id(+) = s.sub_id

The above statement would return all rows from SUBTABLE with corresponding matches in MAIN. If there was no match in MAIN it would return a null row.

Assume the following data is in the tables:

MAIN.id   MAIN.sub_id         SUBTABLE.sub_id    SUBTABLE.text
-------   -----------         ---------------    -------------
1         100                 50                 text50
2         130                 100                text100
3         140                 130                text130
4         150                 150                text150
5         200

The statement from above would have output as follows:

SELECT m.main.id, s.text
  FROM MAIN m, SUBTABLE s
 WHERE m.sub_id = s.sub_id(+)

ID TEXT
---- -----

1     text100
2     text130
3
4     text150

Because the rows for ID = (3,5) had no matching row in SUBTABLE the (+) added a null row to match it. Note that all rows from MAIN were returned .

SELECT m.main.id, s.text
  FROM MAIN m, SUBTABLE s
 WHERE m.sub_id(+) = s.sub_id

ID TEXT
---- -----

      text50
1     text100
2     text130
4     text150

In this case all rows from SUBTABLE were returned but in the one case where there was no row in MAIN to match (sub_id = 50) a null row was added for MAIN.

HTH,
Chris Halioris
Tactics, Inc.
hali_at_tacticsus.com Received on Tue Feb 25 1997 - 00:00:00 CST