Cross Join

A cross join (also called a Cartesian join) is a join of tables without specifying the join condition. In this scenario, the query would return all possible combination of the tables in the SQL query. To see this in action, let's use the following example:

Table Store_Information

store_nameSalesDate
Los Angeles$1500Jan-05-1999
San Diego$250Jan-07-1999
Los Angeles$300Jan-08-1999
Boston$700Jan-08-1999

Table Geography

region_namestore_name
EastBoston
EastNew York
WestLos Angeles
WestSan Diego

The following SQL statement is a Cartesian join between the Store_Information and the Geography tables:

SELECT A1.store_name STORE1, A2.store_name STORE2, A2.Sales SALES
FROM Geography A1
JOIN Store_Information A2

Result:

STORE1   STORE2   SALES
Boston   Los Angeles   $1500
New York   Los Angeles   $1500
Los Angeles   Los Angeles   $1500
San Diego   Los Angeles   $1500
Boston   San Diego   $250
New York   San Diego   $250
Los Angeles   San Diego   $250
San Diego   San Diego   $250
Boston   Los Angeles   $300
New York   Los Angeles   $300
Los Angeles   Los Angeles   $300
San Diego   Los Angeles   $300
Boston   Boston   $700
New York   Boston   $700
Los Angeles   Boston   $700
San Diego   Boston   $700

An alternative way of specifying a cross join is,

SELECT A1.store_name STORE1, A2.store_name STORE2, A2.Sales SALES
FROM Geography A1, Store_Information A2

A cross join is seldom the desired result. Rather, it is an indication that some required join condition is missing in the SQL query.

Next: SQL SELECT UNIQUE