SQL CROSS JOIN

SQL > SQL JOIN > 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_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

Table Geography

Region_Name	Store_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

The following SQL statement is a Cartesian join between the Store_Information and theGeography 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.