MySQL CROSS JOIN with Examples


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_idcustomer_codecustomer_namegroup_id
1#CID_70001Josh Warner2
2#CID_70002Clifton Christensen 
3#CID_70003Iris Wilkerson1
4#CID_70004Lisa Ramsey2
5#CID_70005Glen Wood 
6#CID_70006William Barnett1
7#CID_70007Francis Lewis 
8#CID_70008Charles Harmon3

Groups:

group_idgroup_name
1Wholesales Customers
2Retail Customers
3Regular Customers
4Online 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_idcustomer_codecustomer_namegroup_idgroup_id1group_name
1#CID_70001Josh Warner22Retail Customers
3#CID_70003Iris Wilkerson11Wholesales Customers
4#CID_70004Lisa Ramsey22Retail Customers
6#CID_70006William Barnett11Wholesales Customers
8#CID_70008Charles Harmon33Regular 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.

Leave a Reply

Your email address will not be published. Required fields are marked *