RE: Quick question re outer joins

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Nuno Souto Sent: Friday, July 23, 2004 10:08 AM
To: oracle-l_at_freelists.org
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_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
-----------------------------------------------------------------