Ansi Joins: Inner Join

Description: Examples of an ANSI-style INNER JOIN.

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

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.