Ansi Joins: Full Join

Description: Example of an ANSI-style 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 w3mentor, 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)
          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;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.