CROSS JOIN vs. INNER JOIN: When to Use Which SQL Join?

CROSS JOIN and INNER JOIN in SQL

Understanding CROSS JOIN

CROSS JOIN, also known as Cartesian product, retrieves all possible combinations of rows from two or more tables. It does not filter rows based on any specific criteria, it just multiplies the number of rows in each table. For example, if one table has 5 rows and another table has 3 rows, their CROSS JOIN will return a result set of 15 rows.

Example:

Consider the following form:

<code>Customers:
CustomerID | Age | Gender
--------- | --- | -------
C0        | 25   | Male
C1        | 30   | Female

Movies:
MovieID   | MovieName
-------   | ---------
M0        | Batman
M1        | Superman</code>

A CROSS JOIN between the Customers and Movies tables will produce the following result set:

CustomerID	Age	Gender	MovieID	MovieName
C0	25	Male	M0	Batman
C0	25	Male	M1	Superman
C1	30	Female	M0	Batman
C1	30	Female	M1	Superman

Understanding INNER JOIN

INNER JOIN, also known as an equijoin, filters rows from two tables based on specified equivalence conditions. It matches those rows that have the same value in the joined column. For example, an INNER JOIN of the Customers and Movies tables based on the CustomerID column returns only rows where the CustomerID matches in both tables.

Example:

Considering the same table as before, an INNER JOIN based on CustomerID will produce the following result set:

CustomerID	Age	Gender	MovieID	MovieName
C0	25	Male	M0	Batman

Select CROSS JOIN and INNER JOIN

The choice between CROSS JOIN and INNER JOIN depends on the desired result.

• Use CROSS JOIN ​​when you want to retrieve all possible combinations of rows from multiple tables, regardless of any filtering conditions.
• Use INNER JOIN ​​when you want to filter rows based on a specific equality condition and retrieve only matching rows from both tables.

The above is the detailed content of CROSS JOIN vs. INNER JOIN: When to Use Which SQL Join?. For more information, please follow other related articles on the PHP Chinese website!