Ansi Joins: Cross Join

Description: Example of an ANSI-style CROSS JOIN.

CREATE TABLE table_one (
  col_one NUMBER,
  col_two VARCHAR2(10)
);
 
CREATE TABLE table_two (
  col_three NUMBER,
  col_four  VARCHAR2(10)
);
 
INSERT INTO table_one VALUES ( 1,    'one');
INSERT INTO table_one VALUES ( 2,    'two');
 
INSERT INTO table_two VALUES (10,    'ten');
INSERT INTO table_two VALUES (20, 'twenty');
INSERT INTO table_two VALUES ( 5,   'five');
 
SELECT * FROM
  table_one cross join
  table_two;
 
 
-- Each row from table_one is returned together
-- with each row from table_two:
 
   COL_ONE COL_TWO     COL_THREE COL_FOUR
---------- ---------- ---------- ----------
         1 one                10 ten
         1 one                20 twenty
         1 one                 5 five
         2 two                10 ten
         2 two                20 twenty
         2 two                 5 five

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.