Understanding Cross Joins in SQL: When You Need Every Combination

What a cross join does

A cross join in SQL combines every row from one table with every row from another table. If the first table has 5 rows and the second has 3 rows, the result contains 15 rows — each of the 5 paired with each of the 3. No matching condition is required; the join straightforward creates all possible combinations.

This is different from the joins you probably use most often. An INNER JOIN or LEFT JOIN looks for rows where a condition is true — where a customer ID matches, for example. A cross join ignores conditions entirely and pairs everything with everything else. The result set grows quickly, which is why cross joins are less common but essential for specific tasks.

Key Takeaways

A cross join produces every possible combination of rows from two tables, with no matching condition required.
The result size equals the row count of the first table multiplied by the row count of the second table, so cross joins can create very large result sets.
Cross joins are written as SELECT * FROM table1 CROSS JOIN table2 or SELECT * FROM table1, table2 — both syntaxes produce the same result.
Common uses include generating all possible date-product combinations, creating a calendar grid, or building a list of all possible pairings for comparison.

When you actually need a cross join

The most practical use is generating combinations that don't naturally exist in your data. Suppose you have a table of products and a table of dates. You want to know which products were available on which dates, but your data only records sales. A cross join creates a row for every product-date pair, which you can then compare against actual sales to find gaps.

Another common scenario: you have a list of sizes (small, medium, large) and a list of colors (red, blue, green). A cross join produces every size-color combination so you can see which ones you stock and which ones you're missing. Without the cross join, you'd have to manually write out or calculate all 9 combinations.

Calendar generation is another real-world case. If you have a table of years and a table of months, a cross join creates every year-month pair. Add another cross join with days, and you've built a complete calendar table without typing out thousands of dates.

The syntax: two ways to write it

The explicit way uses the CROSS JOIN keyword:

SELECT * FROM sizes CROSS JOIN colors;

The implicit way uses a comma between tables with no ON clause:

SELECT * FROM sizes, colors;

Both produce identical results. The explicit syntax is clearer about your intent, so it's preferred in most modern code. The comma syntax is older and can be confusing because it looks like a regular join but behaves differently.

You can also add a WHERE clause to filter the results after the cross join is created. This is useful when you want combinations that meet certain conditions — for example, all product-date pairs where the date falls within the product's availability window.

Why cross joins create large result sets

The math is straightforward: if table A has 100 rows and table B has 50 rows, the cross join produces 5,000 rows. If table B has 1,000 rows, you get 100,000 rows. This multiplication happens when ready in SQL, but the result can become unwieldy or slow down your process if you're not careful.

This is why cross joins are usually combined with filtering. You generate the combinations you need, then use a WHERE clause or a subsequent join to narrow the result down to what matters. Without that filtering step, you risk creating a result set so large it times out or consumes too much memory.

Cross join versus other join types

An INNER JOIN returns only rows where the join condition is true. A LEFT JOIN returns all rows from the left table plus matching rows from the right. A cross join returns every combination, period. There's no condition to match on — the join itself is the entire operation.

Think of it this way: INNER and LEFT joins answer "which rows match?" Cross joins answer "what are all the possible pairings?" The question you're trying to answer determines which join you use. If you're looking for customers and their orders, use INNER or LEFT. If you're building a list of every possible pairing for comparison, use CROSS.

A practical example

Imagine you run a store with three sizes (S, M, L) in a sizes table and four colors (red, blue, green, black) in a colors table. You want to know which size-color combinations you currently stock. A cross join creates all 12 possible combinations:

SELECT sizes.size, colors.color FROM sizes CROSS JOIN colors;

This produces 12 rows: S-red, S-blue, S-green, S-black, M-red, M-blue, and so on. You can then join this result against your inventory table to see which combinations you actually have in stock and which ones are missing. Without the cross join, you'd have to manually list all 12 or write a much more complex query.

Performance considerations

Cross joins are fast to execute because SQL doesn't have to evaluate a condition — it straightforward pairs everything. The time cost comes from storing and returning the result set. A cross join of two small tables (under 100 rows each) is negligible. A cross join of two large tables can be slow and memory-intensive.

If you're writing a cross join, ask yourself whether you really need all the combinations or just a subset. If you only need combinations that meet certain criteria, add a WHERE clause to filter early. If you're joining the result against another table anyway, consider whether you can restructure the query to avoid the cross join altogether.

Frequently Asked Questions

Can I cross join more than two tables?

Yes. You can chain cross joins: SELECT * FROM table1 CROSS JOIN table2 CROSS JOIN table3. The result size multiplies each time — if each table has 10 rows, the result has 1,000 rows. Be cautious with three or more tables because the result grows exponentially.

What's the difference between CROSS JOIN and a comma in the FROM clause?

Functionally, they're identical. SELECT * FROM table1 CROSS JOIN table2 and SELECT * FROM table1, table2 produce the same result. The explicit CROSS JOIN syntax is clearer and preferred in modern SQL because it shows your intent when ready.

Can I use a WHERE clause with a cross join?

Yes. The WHERE clause filters the result after the cross join is created. For example, SELECT * FROM dates CROSS JOIN products WHERE product_launch_date <= dates.date creates all combinations and then keeps only those where the product existed on that date.

Is a cross join the same as a Cartesian product?

Yes. A cross join and a Cartesian product are the same thing — both terms describe the operation of combining every row from one set with every row from another. "Cross join" is the SQL term; "Cartesian product" is the mathematical term.

When should I avoid using a cross join?

Avoid cross joins when you're not sure what result size you'll get or when you're joining large tables without a filtering step afterward. If you find yourself writing a cross join and then when ready filtering it down to a tiny subset, you probably don't need the cross join at all — restructure the query instead.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.