A CROSS JOIN in SQL is used to combine every row from one table with every row from another table — it produces what’s called a Cartesian product. Unlike INNER or OUTER joins, it doesn’t use any condition to match rows.
For example, if Table A has 3 rows and Table B has 4 rows, a CROSS JOIN between them will return 3 × 4 = 12 rows — every possible combination.
Here’s a simple example:
SELECT a.ProductName, b.StoreName
FROM Products a
CROSS JOIN Stores b;
This will list every product with every store, even if there’s no relationship between them.
I’ve used CROSS JOIN in a sales forecasting project where we needed to generate a dataset of all possible combinations of products and upcoming months, even if sales hadn’t yet occurred for those months. This allowed us to pre-fill data for future reporting.
One of the main challenges with CROSS JOIN is that it can easily create a huge number of rows if the tables are large. For example, joining two tables with thousands of rows each can produce millions of results, which impacts performance heavily.
A limitation is that it’s not suitable for filtered relationships — you use it only when you truly need all possible combinations.
As an alternative, when I need specific matches or conditional combinations, I use INNER JOIN or LEFT JOIN with proper ON conditions instead.
So, to sum up — CROSS JOIN is powerful but should be used carefully. It’s best for generating combinations, templates, or grids of data — not for relational lookups.