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_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-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:

STORE1STORE2SALES
BostonLos Angeles1500
New YorkLos Angeles1500
Los AngelesLos Angeles1500
San DiegoLos Angeles1500
BostonSan Diego250
New YorkSan Diego250
Los AngelesSan Diego250
San DiegoSan Diego250
BostonLos Angeles300
New YorkLos Angeles300
Los AngelesLos Angeles300
San DiegoLos Angeles300
BostonBoston700
New YorkBoston700
Los AngelesBoston700
San DiegoBoston700

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 String Functions




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact