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 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 INSERT INTO

This page was last updated on June 19, 2023.




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