MySQL: Cross Join

Profile picture for user arilio666

CROSS JOIN is a type of join where it is used to combine two or more tables across a database matching over its commonly defined or we can say that cross join is used to combine every record from a table to another table with its every record. The result of the cross join will be a column with all the matching records of the column from another table. This is yet another type of join where the clause WHERE is not used.

Syntax

The Explicit Syntax

SELECT * FROM TableName_A CROSS JOIN TableName_B;

So here TableName_A will be cross joined with TableName_B aligned with all the common records.

The Implicit Syntax

SELECT * FROM TableName_A, TableName_B;

Both the queries let it be explicit or implicit produces the same result no matter what or how it is used.

A side note is mentioning the explicit way is more clear for us to understand than the implicit query.

Let us dive into a real-time example where we will be focusing on two tables which we are gonna use today and that will be,
The products and shippers table.

Here is what we are gonna do:

  • We are gonna do the cross join between shippers and products and we are gonna need to fetch the names of the products along with their shipper name and their ID as well.

Example

select p.name AS Names, s.name AS ShipperName, s.shipper_id AS SID from products p cross join shippers s limit 10;

  • So we cross joined explicitly using the clause CROSS JOIN with the products and shippers where I have taken product name and aliased it to Names and also the Shippers name and also their ID and limited the result to 10.
  • From here you can see cross join works in a way where all the product's shipper names persisting to that particular product name are displayed first in a simple way it displays the related records of the tables on which we are doing a cross join.
Tags