MySQL CROSS JOIN

MySQL CROSS JOIN is used to combine all possibilities of the two or more tables and returns the result that contains every row from all contributing tables. The CROSS JOIN is also known as CARTESIAN JOIN, which provides the Cartesian product of all associated tables. The Cartesian product can be explained as all rows present in the first table multiplied by all rows present in the second table. It is similar to the Inner Join, where the join condition is not available with this clause.

We can understand it with the following visual representation where CROSS JOIN returns all the records from table1 and table2, and each row is the combination of rows of both tables.

MySQL CROSS JOIN Syntax

The CROSS JOIN keyword is always used with the SELECT statement and must be written after the FROM clause. The following syntax fetches all records from both joining tables:

SELECT column-lists
FROM table1
CROSS JOIN table2;

In the above syntax, the column-lists is the name of the column or field that you want to return and table1 and table2 is the table name from which you fetch the records.

MySQL CROSS JOIN Example

Let us take some examples to understand the working of Left Join or Left Outer Join clause:

CROSS JOIN clause for joining two tables

Here, we are going to create two tables "customers" and "contacts" that contains the following data:

Table: customers

Table: contacts

To fetch all records from both tables, execute the following query:

SELECT *
FROM customers
CROSS JOIN contacts;

After successful execution of the query, it will give the following output:

When the CROSS JOIN statement executed, you will observe that it displays 42 rows. It means seven rows from customers table multiplies by the six rows from the contacts table.

Ambiguous Columns problem in MySQL CROSS JOIN

Sometimes, we need to fetch the selected column records from multiple tables. These tables can contain some column names similar. In that case, MySQL CROSS JOIN statement throws an error: the column name is ambiguous. It means the name of the column is present in both tables, and MySQL gets confused about which column you want to display. The following examples explain it more clearly:

SELECT customer_id, cust_name, income, order_id, price
FROM customer
CROSS JOIN orders;

The above CROSS JOIN throws an error as given in the image below:

This problem can be resolved by using the table name before the column name. The above query can be re-written as:

SELECT customer.customer_id, customer.cust_name, customer.income, orders.order_id, orders.price
FROM customer
CROSS JOIN orders;

After executing the above query, we will get the following output:

LEFT JOIN with WHERE Clause

The WHERE clause is used to return the filter result from the table. The following example illustrates this with the CROSS JOIN clause:

SELECT customers.customer_id, customers.cust_name, customers.income, orders.order_id, orders.price
FROM customers
CROSS JOIN orders
USING(customer_id) WHERE price>1500 AND price<5000;

This statement gives the below result:

MySQL CROSS JOIN Multiple Tables

We have already created two tables named "customers" and "orders". Let us create one more table and name it as "contacts" that contains the following data:

Here, we are going to explain CROSS JOIN with LEFT JOIN using three tables. Execute the following statement to join the three table customers, orders, and contacts. In this statement, first CROSS JOIN completed between orders and contacts, and then LEFT JOIN executes according to the specified condition.

SELECT * FROM customer
LEFT JOIN(orders CROSS JOIN contacts)
ON customer.customer_id=contact_id
ORDER BY income;

After successful execution of the above query, it will give the following output: