Re: outer join

Werner,

thanks for your reply. Yes, I think that this answers my question.

Regards, Michiel

Werner Depoorter 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 table 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.
 

-- 
Michiel Perdeck
------------------------------------------------------------------------
Siemens Nixdorf: perdeck.via_at_sni.de     +31 70 3331530   fax: 3331320
CMG AT Finance : michiel.perdeck_at_cmg.nl +31 20 5033000   fax: 5033011
private        : michiel.perdeck_at_tip.nl +31 30 6570818 pager: 0659676940
or....         : michiel_perdeck_at_hotmail.com
------------------------------------------------------------------------