Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> outer join

outer join

From: Michiel Perdeck <perdeck.via_at_sni.de>
Date: 1997/02/25
Message-ID: <3312BC47.6C0F@sni.de>#1/1

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

-- 
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
------------------------------------------------------------------------
Received on Tue Feb 25 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US