SQL - Cross Join / Cartesian Product

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - 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.

Advertising

3 - Example

3.1 - Data

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 ?

3.2 - Explicit

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

3.3 - 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;
Advertising
data/type/relation/sql/cross_join.txt · Last modified: 2017/09/13 16:15 by gerardnico