CoderZone.org

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

<< lastnext >>

Snippet Name: ANSI Joins: INNER JOIN

Description: Examples of an ANSI-style INNER JOIN.

Also see:
» INDEXES: Bitmap Join Indexes
» ANSI Joins: FULL JOIN
» ANSI Joins: OUTER JOIN
» ANSI Joins: CROSS 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 NUMBER
);
 
CREATE TABLE table_two (
  col_one NUMBER,
  col_two NUMBER
);
 
INSERT INTO table_one VALUES ( 1, 1);
INSERT INTO table_one VALUES ( 3, 5);
INSERT INTO table_one VALUES ( 5, 9);
 
INSERT INTO table_two VALUES ( 4, 5);
INSERT INTO table_two VALUES ( 6, 3);
INSERT INTO table_two VALUES ( 5, 5);
 
SELECT * FROM 
  table_one t1 inner join 
  table_two t2 ON t1.col_one = t2.col_two;
 
   COL_ONE    COL_TWO    COL_ONE    COL_TWO
---------- ---------- ---------- ----------
         5          9          4          5
         3          5          6          3
         5          9          5          5
 
SELECT * FROM 
  table_one t1 inner join 
  table_two t2 using (col_two);
 
-- Note: col_two is only returned once here instead of twice
-- when using is used instead of on. This is because it must 
-- be the same value:
 
   COL_TWO    COL_ONE    COL_ONE
---------- ---------- ----------
         5          3          4
         5          3          5
 
 
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: 8