Ansi Joins: Outer Join

Description: Example of an ANSI-style OUTER JOIN

CREATE TABLE table_one (
  col_one NUMBER,
  col_two CHAR(1)
);
 
CREATE TABLE table_two (
  col_one NUMBER,
  col_two CHAR(1)
);
 
INSERT INTO table_one VALUES (1, 'a');
INSERT INTO table_one VALUES (2, 'b');
INSERT INTO table_one VALUES (3, 'c');
 
INSERT INTO table_two VALUES (2, 'B');
INSERT INTO table_two VALUES (3, 'C');
INSERT INTO table_two VALUES (4, 'D');
 
SELECT * FROM
  table_one t1 left outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
         1 a
 
SELECT * FROM
  table_one t1 right outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
                      4 D
 
SELECT * FROM
  table_one t1 full outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
         1 a
                      4 D

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.