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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nuno Souto Sent: Friday, July 23, 2004 10:07 AM
To: oracle-l_at_freelists.org
Subject: Re: Quick question re outer joins

jo_holvoet_at_amis.com 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_at_optusnet.com.au
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org 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_at_freelists.org
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
-----------------------------------------------------------------