MySQL CROSS JOIN
The CROSS JOIN clause returns the Cartesian product of two or more tables (combines every row from the first table with every row from the second table).
Note: So, if the first table has 100 rows, and the second table has 500 rows, the result set will be 100x500 rows. Be aware that CROSS JOIN can potentially return very large result-sets!
CROSS JOIN Syntax
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
Demo Database
Below is a selection from 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 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 Examples
The following SQL returns the Cartesian product of "Customers" and "Orders":
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;
If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerID=Orders.CustomerID;