Forum Stats

  • 3,757,562 Users
  • 2,251,245 Discussions
  • 7,869,866 Comments

Discussions

full outer joins

623129
623129 Member Posts: 26
edited May 14, 2008 7:17AM in SQL & PL/SQL
Hi,
Can we write a full outer join using oracle properitory join syntax?

Regards,
Sharath

Comments

  • 623129
    623129 Member Posts: 26
    hi,
    then we have to go for sql 1999 syntax only right.
    like this,
    table1 full outer join table 2,

    Regards
  • Paul M.
    Paul M. Member Posts: 10,947
    Yes. See http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3175
  • 94799
    94799 Member Posts: 2,208
    It is possible to write a full outer join prior to ANSI syntax in Oracle but there is no specific syntax for it, see...

    http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219 Bronze Badge
    The code referenced is wrong
    Up through Oracle8i, Oracle programmers have used a workaround to circumvent this limitation. The workaround involves two outer join queries combined by a UNION operator, as in the following example:
    
    SQL> select p.part_id, s.supplier_name
      2  from part p, supplier s
      3  where p.supplier_id = s.supplier_id (+)
      4  union
      5  select p.part_id, s.supplier_name
      6  from part p, supplier s
      7  where p.supplier_id (+) = s.supplier_id;
    
    PART SUPPLIER_NAME
    ---- --------------------
    P1   Supplier#1
    P2   Supplier#2
    P3
    P4
         Supplier#3
    UNION is not the appropriate operator, because it removes duplicates. Use union all
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6585774577187
    SQL> create table t1 as
      2    select 1 x, 1 y from dual union all
      3    select 2,2 from dual union all
      4    select 2,2 from dual;
    
    Table created.
    
    SQL> create table t2 as
      2    select 1 y, 1 z from dual union all
      3    select 3,3 from dual;
    
    Table created.
    
    SQL> 
    SQL> select * from t1 full outer join t2 on (t1.y=t2.y);
    
             X          Y          Y          Z
    ---------- ---------- ---------- ----------
             1          1          1          1
             2          2
             2          2
                                   3          3
    
    SQL> select * from t1,t2 where t1.y=t2.y(+)
      2  union
      3  select * from t1,t2 where t1.y(+)=t2.y;
    
             X          Y          Y          Z
    ---------- ---------- ---------- ----------
             1          1          1          1
             2          2
                                   3          3
    
    SQL> select * from t1,t2 where t1.y=t2.y(+)
      2  union all
      3  select * from t1,t2 where t1.y(+)=t2.y and t1.y is null;
    
             X          Y          Y          Z
    ---------- ---------- ---------- ----------
             1          1          1          1
             2          2
             2          2
                                   3          3
    Clearly, UNION removes duplicates where FULL OUTER JOIN does not
  • 94799
    94799 Member Posts: 2,208
    You are correct Laurent, I should have checked it more closely.

    Thanks for the correction :-)
This discussion has been closed.