Cross JOIN

Oracle Tips by Burleson http://www.dba-oracle.com/t_garmany_9_sql_cross_join.htm

Cartesian Product

The Cartesian product, also referred to as a cross-join, returns all the rows in all the tables listed in the query.  Each row in the first table is paired with all the rows in the second table.  This happens when there is no relationship defined between the two tables.  Both the AUTHOR and STORE tables have ten rows.  If we use a Cartesian join in these two tables, we will get back 100 rows.   

SQL> select

  2    author_key,

  3    store_key

  4  from

  5    author, store;

AUTHOR_KEY  STOR

----------- ----

A101        S101

A101        S102

A101        S103

A101        S104

A101        S105

A101        S106

A101        S107

A101        S108

A101        S109

A101        S110

A102        S101

A102        S102

…

A110        S105

A110        S106

A110        S107

A110        S108

A110        S109

A110        S110

100 rows selected.

Most of the time, we do not want a Cartesian join, and we end up with one because we failed to provide a filter on the join.  If we actually want a Cartesian join, then we should use the ANSI cross join to tell others reading the script that we actually wanted a Cartesian join.

select

  author_key,

  store_key

from

  author cross join store;

One reason to use a Cartesian join is to generate a large amount of rows to use for testing.  I can take a large table and cross join it to another large table and produce a very large results set.  If I cross join dba_objects and dba_views, I can produce the results set below.

SQL> select count(*)

  2  from

  3    dba_objects cross join dba_views;

  COUNT(*)

----------

 164623840

That’s a lot of rows!