SQL - Cross Join / Cartesian Product

About

A cross-join (also called Cartesian join) occurs when a request does not have a join condition between two tables. We say that the inner table is not driven from (not dependent on) the outer table.

That creates a Cartesian product of the tables involved in the join. The size of a Cartesian product is the number of rows in the first table multiplied by the number of rows in the second table.

In other word, if the inner table's access path is independent of the outer table, then the same rows are retrieved for every iteration of the outer loop.

Articles Related

Example

table1 (inner set)	table2 (outer set)
Column ID	Column ID	Column ID_2
A	A
B	B	A
D	C	C

gerardnico@orcl>select table1.id "ID_table1", table2.id "ID_table2"
  2        FROM table1, table2;
 
ID_table1  ID_table2
---------- ----------
A          A
A          B
A          C
B          A
B          B
B          C
D          A
D          B
D          C
 
9 rows selected.

We have 3 rows from table1 to multiply by 3 rows in the table2.

Real example of cross-join in this article : OWB - How to load a fact table for a data quality cube ?