What is a CROSS JOIN, and what result does it produce?
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.
- 1
• Produces all possible combinations of rows between two tables.
- 2
• No ON condition is used (though MySQL allows it syntactically).
- 3
• If table A has X rows and table B has Y rows, the result has X × Y rows.
- 1
Example:
- 2
- 3
SELECT *
- 4
FROM colors
- 5
CROSS JOIN sizes;
- 6
- 7
If 'colors' has 3 rows and 'sizes' has 4 rows, the output will contain 12 rows.
- 1
• To generate combinations (e.g., product variations).
- 2
• To create test datasets.
- 3
• 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.
Scenario Questions
0-2 years experience
- 1We have a table `users` with 5 rows and a table `roles` with 3 rows. How would you write a query to list every possible user‑role combination, and what would the result look like?
- 2If you accidentally write a CROSS JOIN between `orders` (1 million rows) and `products` (10 thousand rows), what happens to the result set size and query performance?
- 3Given tables A and B, how does a CROSS JOIN differ from an INNER JOIN that has no ON condition?
2-5 years experience
- 1Our reporting feature needs a matrix of dates crossed with time slots. How would you implement this with a CROSS JOIN while ensuring the result set stays manageable?
- 2A dashboard that used a CROSS JOIN started timing out after the data grew. Walk me through how you would diagnose the issue and what changes you might make.
- 3Why might a query that was intended to be an INNER JOIN end up returning far more rows than expected? Explain the debugging steps you would take.
5-8 years experience
- 1Our analytics pipeline generates all product‑category pairs using a CROSS JOIN before filtering, and it now causes memory pressure. How would you redesign the query or pipeline to avoid the large Cartesian product?
- 2When MySQL tables are sharded across multiple nodes, how does a CROSS JOIN behave, and what strategies can you use to prevent cross‑node Cartesian products?
- 3Discuss the trade‑offs of keeping the combination logic in a CROSS JOIN versus generating the pairs in application code for a large dataset.
8+ years experience
- 1We are migrating a monolith that heavily relies on CROSS JOINs for test‑data generation into a microservices architecture with separate databases. What architectural considerations and migration steps would you propose to handle the Cartesian‑product logic while preserving performance and data integrity?
- 2Multiple teams use CROSS JOINs to build feature‑flag matrices, creating hidden coupling and scaling issues. How would you establish cross‑team guidelines or refactor the approach to reduce impact?
- 3In a long‑term data‑warehouse strategy, how would you evaluate the decision to keep CROSS JOIN‑based combinatorial tables versus materializing the combinations as static tables, considering storage cost, query latency, and future schema changes?
Follow-up Questions
- What are some safer alternatives if you only need a subset of the Cartesian product?
- How would you limit the number of rows returned by a CROSS JOIN?
- Can you describe how the MySQL optimizer treats a CROSS JOIN compared to an INNER JOIN without a predicate?