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.
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.
Let us take some examples to understand the working of Left Join or Left Outer Join clause:
Here, we are going to create two tables "customers" and "contacts" that contains the following data:
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.
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:
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:
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: