|
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 | 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 SELECT UNIQUE
|
| Copyright © 2013 1keydata.com All Rights Reserved.
Privacy Policy |
|