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