MySQL CROSS JOIN
MySQL CROSS JOIN Keyword
All records from both tables are returned using the CROSS JOIN keyword (table1 and table2).
CROSS JOIN Syntax
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
Note: CROSS JOIN can potentially return very large result-sets!
Demo Database
We’ll be using the well-known Northwind sample database in this tutorial.
A sample from the “Customers” table is shown below:
| 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 a selection from 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 |
MySQL CROSS JOIN Example
All clients and all orders are selected using the SQL statement that follows:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;
Note: Regardless of whether the other table matches or not, the CROSS JOIN keyword returns all matching records from both tables. Therefore, such rows will also be listed if there are rows in “Customers” that do not have matching rows in “Orders,” or if there are rows in “Orders” that do not match rows in “Customers.”
In the event that there is a relationship between tables 1 and 2, adding a WHERE clause will cause the CROSS JOIN to yield the same outcome as the INNER JOIN clause:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerID=Orders.CustomerID;
