Understanding CROSS JOIN in MySQL
A CROSS JOIN in MySQL returns the Cartesian product of two tables. This means every row from the first table is combined with every row from the second table.
• Produces all possible combinations of rows between two tables.
• No ON condition is used (though MySQL allows it syntactically).
• If table A has X rows and table B has Y rows, the result has X × Y rows.
Example:
SELECT *
FROM colors
CROSS JOIN sizes;
If 'colors' has 3 rows and 'sizes' has 4 rows, the output will contain 12 rows.
• To generate combinations (e.g., product variations).
• To create test datasets.
• When explicitly needed for Cartesian product logic.
In short, a CROSS JOIN creates every possible row combination between two tables, which can grow very large quickly if the tables contain many rows.