MySQL CROSS JOIN
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;
Try it Yourself »
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;
Try it Yourself »
