CoderZone.org

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

<< lastnext >>

Snippet Name: ANSI Joins: FULL JOIN

Description: Example of an ANSI-style FULL JOIN.

Also see:
» INDEXES: Bitmap Join Indexes
» ANSI Joins: OUTER 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 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)
          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;
 
 
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