CoderZone.org

Category: >> Oracle PL/SQL >> ANSI Joins: OUTER JOIN Bookmark and Share

<< lastnext >>

Snippet Name: ANSI Joins: OUTER JOIN

Description: Example of an ANSI-style OUTER JOIN

Also see:
» INDEXES: Bitmap Join Indexes
» ANSI Joins: FULL JOIN
» ANSI Joins: CROSS JOIN
» ANSI Joins: INNER JOIN
» Self-join example and syntax
» FULL JOIN example and syntax
» RIGHT JOIN example and syntax
» LEFT JOIN example and syntax
» INNER JOIN example and syntax
» WHERE Clause - Joins
» Dates: Oddball Stuff
» Date Functions: WHERE Clause Joins

Comment: (none)

Author: CoderZone
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 10th, 2009

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
 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 7