A CROSS JOIN in MySQL produces the Cartesian product of two tables, meaning every row in the first table is paired with every row in the second table. This type of join does not require a specific condition to join the tables. It results in a combination of all rows from both tables, which can result in a large dataset if the tables have many rows.

Syntax

SELECT column_list
FROM table1
CROSS JOIN table2;

Or equivalently:

SELECT column_list
FROM table1, table2;

Use Case

CROSS JOIN is typically used when you need all possible combinations of rows from two tables. This can be useful for generating test data, pairing combinations, or scenarios where every combination is meaningful (e.g., generating schedules, permutations, etc.).

Example: Using the Northwind Database

Let’s consider two tables from the Northwind database:

1. Categories table: CategoryID CategoryName 1 Beverages 2 Condiments 3 Confections
2. Suppliers table: SupplierID SupplierName 101 Exotic Liquids 102 New Orleans Cajun 103 Grandma’s Kitchen

Query: CROSS JOIN Example

SELECT Categories.CategoryName, Suppliers.SupplierName
FROM Categories
CROSS JOIN Suppliers;

Result:

The query generates all possible combinations of rows from the Categories and Suppliers tables.

Key Points

1. Cartesian Product: The number of rows in the result is the product of the rows in both tables. For example, if Categories has 3 rows and Suppliers has 3 rows, the result will have 3×3=93 \times 3 = 9 rows.
2. Performance Impact: Be cautious with large tables, as the Cartesian product can grow exponentially and impact performance.
3. Practical Use: Often, CROSS JOIN is combined with filtering criteria using a WHERE clause to reduce the result set.

Adding a Filter

To filter the results, you can add a WHERE clause:

Query with Filter

SELECT Categories.CategoryName, Suppliers.SupplierName
FROM Categories
CROSS JOIN Suppliers
WHERE Categories.CategoryName = 'Beverages';

Result:

Difference from Other Joins

• Unlike INNER JOIN, LEFT JOIN, or RIGHT JOIN, the CROSS JOIN does not require a matching condition between the two tables.
• The result contains all combinations, regardless of any logical relationship between the tables.

Conclusion

While CROSS JOIN can generate useful combinations, it should be used carefully with large datasets to avoid performance issues.