Curriculum
Course: MYSQL
Login

Curriculum

MYSQL

MySQL References

0/140
Text lesson

MySQL CROSS JOIN

SQL CROSS JOIN Keyword

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

                                                    img_cross_join

CROSS JOIN Syntax

SELECT column_name(s)
FROM table1
CROSS JOIN table2;
Note: CROSS JOIN can potentially generate very large result sets!

Demo Database

In this tutorial, we’ll be using the well-known Northwind sample database.

Here is a portion of 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 here is a portion of 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

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 possible combinations of rows from both tables, regardless of whether there is a match. Therefore, if there are rows in “Customers” that do not have matches in “Orders”, or rows in “Orders” that do not have matches in “Customers”, those rows will still be included.

If you add a WHERE clause (if there is a relationship between table1 and table2), the CROSS JOIN will produce the same result as an INNER JOIN clause.

Example

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