CROSS JOIN (without a WHERE clause or ON clause in join condition) produces a result set where its size is the number of rows in the first table multiplying the number of rows in the second table. This type of result is called Cartesian Product of two tables (Table 1 x Table 2). A practical example of CROSS JOIN is to build a junk dimension in a data warehouse environment where all the combination possibilities need to be worked out and stored.

Let's visually illustrate. Say we have two tables cross-joined together as below.

Because there are 3 rows in each table so cross join produces 9 rows.

When using CROSS JOIN in MySQL, pay attention to the following guidelines.

1. Normally we don't use CROSS JOIN with ON clause. If you do use ON clause with a CROSS JOIN, it becomes INNER JOIN when the columns used have Foreign Key Relationships.

   As MySQL manual stated, in MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

2. If you store the result of a CROSS JOIN in a table, consider if you need to periodically update the table to keep up with new data or change of data in the contributing tables.
3. CROSS JOIN can produce large dataset so use with caution.

In example below, Products table cross joins with Suppliers table to work out how many ways that a product can be provided by a supplier. The result has total 2,233 rows because Products table has 77 rows and Suppliers table has 29 rows, 77 x 29 = 2,233.

select t1.ProductID, t1.ProductName, t2.CompanyName as SupplierName
from products as t1
cross join suppliers as t2
order by SupplierName;

Query result set - 2,233 rows returned:

However, if you add ON clause in the CROSS JOIN, it becomes an INNER JOIN.

select t1.ProductID, t1.ProductName, t2.CompanyName as SupplierName 
from products as t1
cross join suppliers as t2 on t1.SupplierID=t2.SupplierID;
 
-- Or, you can use WHERE clause like below which produces the same result.
 
select t1.ProductID, t1.ProductName, t2.CompanyName as SupplierName
from products as t1
cross join suppliers as t2
where t1.SupplierID=t2.SupplierID;

Query result set - 77 rows returned:

Happy Coding!

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Another Example for Outer Joins (three tables)

4. Using Self Joins to Combine Data from the Same Table

5. SQL Set Operators - a Visual Guide to UNION, UNION ALL, MIMUS/EXCEPT, INTERSECT

6. Using UNION to Append Result Sets

7. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

8. How to do MINUS/EXCEPT and INTERSECT in MySQL

9. How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows

10. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

11. How to Simulate Full Join in MySQL - Part 3: use UNION to simulate FULL JOIN

12. Differences Between Join and Union

Back to Tutorial Index Page