-
MySQL Tutorial
MySQL SQL
- MySQL SQL
- MySQL SELECT
- MySQL WHERE
- MySQL AND, OR, NOT
- MySQL ORDER BY
- MySQL INSERT INTO
- MySQL NULL Values
- MySQL UPDATE
- MySQL DELETE
- MySQL LIMIT
- MySQL MIN and MAX
- MySQL COUNT, AVG, SUM
- MySQL LIKE
- MySQL Wildcards
- MySQL IN
- MySQL BETWEEN
- MySQL Aliases
- MySQL Joins
- MySQL INNER JOIN
- MySQL LEFT JOIN
- MySQL RIGHT JOIN
- MySQL CROSS JOIN
- MySQL Self Join
- MySQL UNION
- MySQL GROUP BY
- MySQL HAVING
- MySQL EXISTS
- MySQL ANY, ALL
- MySQL INSERT SELECT
- MySQL CASE
- MySQL Null Functions
- MySQL Comments
- MySQL Operators
MySQL Database
- MySQL Create DB
- MySQL Drop DB
- MySQL Create Table
- MySQL Drop Table
- MySQL Alter Table
- MySQL Constraints
- MySQL Not Null
- MySQL Unique
- MySQL Primary Key
- MySQL Foreign Key
- MySQL Check
- MySQL Default
- MySQL Create Index
- MySQL Auto Increment
- MySQL Dates
- MySQL Views
MySQL References
MySQL Examples
MySQL CROSS JOIN Keyword
The CROSS JOIN keyword returns all records from both tables (table1 and table2)
SQL CROSS JOIN Keyword
The CROSS JOIN keyword returns all records
from both tables (table1 and table2).
CROSS JOIN Syntax
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
Note: CROSS JOIN can potentially return very large
result-sets!
Demo Database
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 |
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;