MySQL CROSS JOIN Keyword

MySQL CROSS JOIN Keyword

The CROSS JOIN keyword returns all records from both tables (table1 and table2).

MySQL CROSS JOIN is used to combine all possibilities of the two or more tables and returns the result that contains every row from all contributing tables. The CROSS JOIN is also known as CARTESIAN JOIN, which provides the Cartesian product of all associated tables. The Cartesian product can be explained as all rows present in the first table multiplied by all rows present in the second table. It is similar to the Inner Join, where the join condition is not available with this clause.

CROSS JOIN Syntax

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Demo Database

In this tutorial we will use the well-known sample database.

Below is a selection from the “Customers” table:

Customers

And a selection from the “Orders” table:

Orders

MySQL CROSS JOIN Example

The following SQL statement selects all customers, and all orders:

Example

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:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerID=Orders.CustomerID;