How to Use the MySQL CROSS JOIN
In this tutorial, we'll learn how to use the CROSS JOIN in MySQL. CROSS JOIN returns the Cartesian product of 2 tables, returning all possible combination of all records.
For example, suppose we join the employees table, containing 4 records, and departments table, containing 3 records. Since CROSS JOIN returns all combination of all records from both employees and departments, the number of records will be 12 (4 x 3).
MySQL CROSS JOIN Syntax
The CROSS JOIN syntax is as follows:
SELECT * FROM table_1
CROSS JOIN table_2;
Note that the CROSS JOIN clause doesn't have join conditions.
If we add a WHERE clause as shown in the following statement, the CROSS JOIN works like the INNER JOIN clause.
Examples
Suppose there are 2 tables, customers and groups. The customers table consists of 4 columns, customer_id, customer_code, customer_name and group_id. The group_id column can be NULL value. And the groups table consists of 2 columns, group_id and group_name. The group_id column in the customers table is referenced to the group_id column in the groups table.
Customers:
| customer_id | customer_code | customer_name | group_id |
|---|---|---|---|
| 1 | #CID_70001 | Josh Warner | 2 |
| 2 | #CID_70002 | Clifton Christensen | |
| 3 | #CID_70003 | Iris Wilkerson | 1 |
| 4 | #CID_70004 | Lisa Ramsey | 2 |
| 5 | #CID_70005 | Glen Wood | |
| 6 | #CID_70006 | William Barnett | 1 |
| 7 | #CID_70007 | Francis Lewis | |
| 8 | #CID_70008 | Charles Harmon | 3 |
Groups:
| group_id | group_name |
|---|---|
| 1 | Wholesales Customers |
| 2 | Retail Customers |
| 3 | Regular Customers |
| 4 | Online Customers |
Example 1
The following statement returns all possible combination of all records from both tables, customers and groups:
SELECT * FROM customers
CROSS JOIN
groups;
The result set from this statement contains 32 records, which the combination of 8 and 4.
Example 2
Let's look at another statement below:
SELECT * FROM customers
CROSS JOIN groups
WHERE customers.group_id = groups.group_id;
In this statement, as we can see, we specified the WHERE condition. In this case, the CROSS JOIN works like INNER JOIN clause.
| customer_id | customer_code | customer_name | group_id | group_id1 | group_name |
|---|---|---|---|---|---|
| 1 | #CID_70001 | Josh Warner | 2 | 2 | Retail Customers |
| 3 | #CID_70003 | Iris Wilkerson | 1 | 1 | Wholesales Customers |
| 4 | #CID_70004 | Lisa Ramsey | 2 | 2 | Retail Customers |
| 6 | #CID_70006 | William Barnett | 1 | 1 | Wholesales Customers |
| 8 | #CID_70008 | Charles Harmon | 3 | 3 | Regular Customers |
The result set from this statement is as follows:
In this tutorial, we've learned how to use the MySQL CROSS JOIN. You can use CROSS JOIN when you want to return the Cartesian product of 2 tables, returning all possible combination of all records. There are some other JOIN clauses in MySQL, such as LEFT JOIN, RIGHT JOIN and INNER JOIN.