RE: Quick question re outer joins
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Jul 2004 10:32:13 -0400
As far as I know the only way to get a full outer join in Oracle is to use
the ANSI full outer join syntax introduced in version 9. Before this you
had to union what amounted to a left and a right outer join. A full outer
join being defined as all the rows of A and all the rows of B:
SQL> select d.deptno, e.ename
2 from dept d FULL OUTER JOIN emp e
3 on d.deptno = e.deptno
4 order by d.deptno ;
DEPTNO ENAME
---------- ----------
10 CLARK
10 MILLER
20 SMITH
20 SCOTT
20 FORD
20 ADAMS
20 JONES
30 ALLEN
30 JAMES
30 TURNER
. 30 BLAKE
30 MARTIN
30 WARD
40
KING
15 rows selected.
Looking back at the thread I think several of the posts are actually in
agreement but what is/was meant by "extra rows" is actually the problem.
One of, if not the first post on the thread, asked if I read it correctly if
the row set returned by the outer join should match the count returned in A.
The answer is not always as the join to B could cause additional rows to
appear in the result set when there are multiple rows present in B for the
join condition. An outer join will give you back the inner join plus those
rows in A that do not have a maching row in B where A is the table where we
always want to return a row from in the result set.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Nuno Souto
Sent: Friday, July 23, 2004 10:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Quick question re outer joins
Lex de Haan apparently said,on my timestamp of 23/07/2004 6:59 PM:
> indeed -- just start with "select ename from scott.emp" to get 14 rows,
> and then outer join with scott.dept -- I am pretty sure you'll see 15
rows.
Nope. 14.
> not sure whether you are talking about the left, right, or full outer
join,
> though ...
full, I guess. The one you get by using (+) with Oracle.
--
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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
