The CROSS JOIN keyword returns all possible combinations of records from both tables (table1 and table2).
SELECT column_name(s) FROM table1 CROSS JOIN table2; |
Note: CROSS JOIN can potentially generate very large result sets! |
In this tutorial, we’ll be using the well-known Northwind sample database.
Here is a portion of the “Customers” table:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
And here is a portion of the “Orders” table:
OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
10308 |
2 |
7 |
1996-09-18 |
3 |
10309 |
37 |
3 |
1996-09-19 |
1 |
10310 |
77 |
8 |
1996-09-20 |
2 |
The following SQL statement selects all customers and all orders:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers CROSS JOIN Orders; |
Note: The CROSS JOIN keyword returns all possible combinations of rows from both tables, regardless of whether there is a match. Therefore, if there are rows in “Customers” that do not have matches in “Orders”, or rows in “Orders” that do not have matches in “Customers”, those rows will still be included.
If you add a WHERE clause (if there is a relationship between table1 and table2), the CROSS JOIN will produce the same result as an INNER JOIN clause.
SELECT Customers.CustomerName, Orders.OrderID FROM Customers CROSS JOIN Orders WHERE Customers.CustomerID=Orders.CustomerID; |