RE: Quick question re outer joins

Hi Nuno,
Try this:

select e.ename, d.deptno
from emp e, dept d
where e.deptno = d.deptno

ENAME   DEPTNO
MILLER  10
KING    10
CLARK   10
FORD    20
ADAMS   20
SCOTT   20
JONES   20
SMITH   20
JAMES   30
TURNER  30
BLAKE   30
MARTIN  30
WARD    30
ALLEN   30

14 rows returned
 
select e.ename, d.deptno
from emp e, dept d
where e.deptno (+) = d.deptno

ENAME   DEPTNO
SMITH   20
ALLEN   30
WARD    30
JONES   20
MARTIN  30
BLAKE   30
CLARK   10
SCOTT   20
KING    10
TURNER  30
ADAMS   20
JAMES   30
FORD    20
MILLER  10
        40  <<<<<< note that ename is null

15 rows returned.

The outer join in this case, said give me all dept records and matching
emp records.  If I don't find a matching emp record, then populate emp
columns with NULL.

Now if you put the outer join as 'where e.deptno = d.deptno (+)' then
you will see 14 records again.  But, if you set emp.deptno to null for
some employee records, then you will see those employee records, but
with a NULL value for the d.deptno column.

So to answer your original question, Yes.  Using an outer join can add
records to your result set.

HTH,

Alan



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nuno Souto
Sent: Friday, July 23, 2004 10:07 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Quick question re outer joins

jo_holvoet@xxxxxxxx apparently said,on my timestamp of 23/07/2004 6:50
PM:

> If the relationship to the new table is 1-N, it could potentially add 
> rows just like a regular join, couldn't it ?

How?
If it finds a corresponding row, it puts its column value in the output.
If it doesn't, it puts a NULL there.  How can that add rows?

--
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision@xxxxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: