Efficiently merge data with MySQL CROSS JOIN! Master this powerful keyword through practical examples and clear explanations, optimizing your database queries.
The CROSS JOIN
keyword returns all records from both tables (table1 and table2).
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
Note: CROSS JOIN
can potentially return very large result-sets!
In this tutorial we will use the well-known Northwind sample 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 |
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 matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
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:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerID=Orders.CustomerID;
#mysql #sql