SQL - Cross Join / Cartesian Product

About

Example

Data

The data below is used in the explicit and implicit example

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

And we want:

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 ?

Explicit

SELECT
	  table1.id "ID_table1"
	, table2.id "ID_table2"
	FROM
	  table1
	  CROSS JOIN table2;

Implicit

  • There is no join condition
select table1.id "ID_table1", table2.id "ID_table2"
from table1, table2
  • in case of when the application controls that you have set a join condition, you can add the 1=1 predicate.
select table1.id "ID_table1", table2.id "ID_table2"
from table1, table2
where 1=1;

Powered by ComboStrap