Cross Join | cross join SQL | Join - sql - sql tutorial - learn sql
- A cross join (also called a Cartesian join) is a join of tables without specifying the join condition,the query would return all possible combination of the tables in the SQL query.
- The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.
- If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
- An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.
Example
Table Product_Information
Product_Name | price | Txn_Date |
---|---|---|
Los Angeles | 1500 | Jan-05-2017 |
San Diego | 250 | Jan-07-2017 |
Los Angeles | 300 | Jan-08-2017 |
Boston | 700 | Jan-08-2017 |
Table Characteristics
Region_Name | Product_Name |
---|---|
East | Boston |
East | New York |
West | Los Angeles |
West | San Diego |
- The following SQL statement is a Cartesian join between
Result:
Product1 | Product2 | price |
---|---|---|
Boston | Los Angeles | 1500 |
New York | Los Angeles | 1500 |
Los Angeles | Los Angeles | 1500 |
San Diego | Los Angeles | 1500 |
Boston | New York | 250 |
New York | Los Angeles | 250 |
Los Angeles | Los Angeles | 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
- Ø A cross join is seldom the desired result. Rather, it is an indication that some required join condition is missing in the SQL query.