René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

ANSI full join

create table left_tbl (
  id  number,
  txt varchar2(10)
);

create table right_tbl (
  id  number,
  txt varchar2(10)
);

  insert into  left_tbl values (1, 'one'   );
  insert into  left_tbl values (2, 'two'   );
  insert into  left_tbl values (3, 'three' );
--insert into  left_tbl values (4, 'four'  );
  insert into  left_tbl values (5, 'five'  );

  insert into right_tbl values (1, 'uno'   );
--insert into right_tbl values (2, 'dos'   );
  insert into right_tbl values (3, 'tres'  );
  insert into right_tbl values (4, 'cuatro');
  insert into right_tbl values (5, 'cinco' );
A full join returns the records of both tables (that satisfy a [potential] where condition). In the following example, 4 cuatro and 2 two are returned, although the ids 4 and 2 are not present in both tables: 
select
             id,
           l.txt,
           r.txt
  from
            left_tbl l full join
           right_tbl r using(id)
 ##Aorder by,/ora/sql/order_by.html)
          id;

        ID TXT        TXT
---------- ---------- ----------
         1 one        uno
         2 two
         3 three      tres
         4            cuatro
         5 five       cinco

drop table  left_tbl;
drop table right_tbl;

Links

See also other ANSI joins.