Cross join

 

A cross join, also known as a Cartesian Product join, returns a result table where each row from the first table is combined with each row from the second table.

The number of rows in the result table is the product of the number of rows in each table. If the tables involved are large, this join can take a very long time.

A cross join can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

Suppose that the following tables exist.

Table 1. Table A

ACOL1	ACOL2
A1	AA1
A2	AA2
A3	AA3

Table 2. Table B

BCOL1	BCOL2
B1	BB1
B2	BB2

The following two select statements produce identical results.

    SELECT * FROM A CROSS JOIN B

    SELECT * FROM A, B

The result table for either of these SELECT statements looks like this.

ACOL1	ACOL2	BCOL1	BCOL2
A1	AA1	B1	BB1
A1	AA1	B2	BB2
A2	AA2	B1	BB1
A2	AA2	B2	BB2
A3	AA3	B1	BB1
A3	AA3	B2	BB2

 

Parent topic:

Joining data from more than one table