Re: outer join

> 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?

You have to place the (+) sign after the column for which Oracle has to generate NULL values. In the case you mentioned, Oracle must generate a NULL columns for s.sub_id, so you have to place a (+) after s.sub_id. You can also see it in this way: the plus must be put after the column for which Oracle has to generate 'more' rows than there are in the database.

Werner. Received on Tue Feb 25 1997 - 00:00:00 CST