What a Cross Join Does in SQL and When You Actually Need One

A cross join combines every row from one table with every row from another

A cross join in SQL takes all rows from your first table and pairs each one with all rows from your second table. If your first table has 5 rows and your second has 3 rows, the result has 15 rows — every possible combination. Most databases call this a Cartesian product, which is the mathematical term for exactly this kind of pairing.

The syntax is straightforward. You write SELECT * FROM table1 CROSS JOIN table2, or the older style SELECT * FROM table1, table2 (the comma syntax also produces a cross join, though it is less clear to read). Some databases like PostgreSQL and MySQL support both. SQL Server supports both as well. The result includes every column from both tables, with no filtering based on matching values.

Cross joins are rare in real work because they produce large result sets quickly. A cross join of a 1,000-row table with a 1,000-row table gives you 1 million rows. But when you need them, they solve specific problems that other join types cannot.

Key Takeaways

A cross join pairs every row from the first table with every row from the second table, creating all possible combinations.
The result size grows by multiplication: 100 rows × 50 rows = 5,000 result rows, so cross joins can become very large very quickly.
You write a cross join with the syntax CROSS JOIN or by listing tables separated by commas in the FROM clause.
Cross joins are useful for generating sequences, creating all possible combinations for reports, or building lookup tables where you need every pairing.
Most of the time when you think you need a cross join, you actually need an INNER JOIN or LEFT JOIN that filters on a matching column.

When a cross join actually solves your problem

The most common real use is generating combinations. Suppose you have a table of product sizes (Small, Medium, Large) and a table of colors (Red, Blue, Green). A cross join gives you every size-color combination so you can create SKUs or inventory records for each pairing. You write SELECT sizes.size, colors.color FROM sizes CROSS JOIN colors and you get 9 rows: Small-Red, Small-Blue, Small-Green, Medium-Red, and so on.

Another case is building a calendar or date range. If you have a table of years and a table of months, a cross join produces every year-month combination. You can then use that to find which months had no sales, or to create a complete schedule even when some periods have no data.

A third case is creating a lookup or reference table. You might cross join a list of stores with a list of product categories to build a table that says "every store should stock every category." Then you can compare that to actual inventory to find gaps.

In all these cases, you genuinely want every combination. If you only wanted matching pairs — like "show me each product with the color it actually comes in" — you would use an INNER JOIN instead, matching on a column that links the two tables.

How cross joins differ from other join types

An INNER JOIN requires a matching condition. You write SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id. The result includes only rows where the condition is true — only orders that have a matching customer. A cross join has no ON clause and no condition; it includes all rows regardless.

A LEFT JOIN keeps all rows from the left table and adds matching rows from the right table, filling in NULL where there is no match. A cross join does not filter at all; it multiplies the tables together.

The key difference: other joins filter the result based on a relationship between the tables. Cross joins do not assume any relationship. They simply create every possible pairing. This is why cross joins are uncommon — most real data problems involve finding related records, not generating all possible combinations.

Why cross joins produce large results so quickly

The size grows by multiplication, not addition. If you cross join a 100-row table with a 50-row table, you get 5,000 rows. If you cross join a 1,000-row table with a 1,000-row table, you get 1 million rows. This is why you should always think about the size of your tables before writing a cross join.

In practice, this means cross joins are usually safe only when at least one table is small. A cross join of a 3-row color table with a 50-row size table produces 150 rows — manageable. A cross join of two large production tables will either time out or consume so much memory that your database slows down for everyone else.

If you find yourself writing a cross join and one of your tables has thousands of rows, stop and reconsider. You probably need a different join type, or you need to filter one of the tables first using a WHERE clause before the join.

Writing a cross join in different SQL databases

Most databases support the explicit CROSS JOIN syntax, which is the clearest way to write it. PostgreSQL, MySQL, SQL Server, and SQLite all accept SELECT * FROM table1 CROSS JOIN table2.

The older comma syntax SELECT * FROM table1, table2 also produces a cross join in all these databases. It is less obvious to someone reading the code, so the explicit CROSS JOIN syntax is preferred. Some teams have style guides that forbid the comma syntax for exactly this reason — it is too easy to miss that you are creating a cross join.

Oracle, PostgreSQL, and SQL Server all handle cross joins the same way. The syntax is consistent across these platforms, so if you learn it in one, you can use it in the others.

Common mistakes when using cross joins

The most common mistake is writing a cross join by accident. You meant to write an INNER JOIN but forgot the ON clause, and now you have a cross join that produces millions of rows. This is why many teams add a rule: if you write a comma in the FROM clause, you must also write an ON clause. The explicit CROSS JOIN syntax makes your intent clear and prevents this mistake.

Another mistake is not thinking about the result size. You cross join two tables that seemed small, but one of them grows over time as more data is added. Six months later, the query that used to run in a second now times out. If you use a cross join, document why you need it and monitor the table sizes.

A third mistake is using a cross join when you actually need a different join type. You want to show each customer with their orders, but you write a cross join instead of a LEFT JOIN. Now you get every customer paired with every order, not just their own orders. The result is wrong and confusing.

Frequently Asked Questions

Is a cross join the same as a Cartesian product?

Yes. Cartesian product is the mathematical term for what a cross join does — it creates all possible pairs of rows from two sets. In SQL, "cross join" and "Cartesian product" mean the same thing.

Can I use a WHERE clause with a cross join?

Yes. You can write SELECT * FROM table1 CROSS JOIN table2 WHERE condition. The cross join happens first, creating all combinations, then the WHERE clause filters the result. However, if you are filtering based on a relationship between the tables, you should use an INNER JOIN with an ON clause instead — it is clearer and often faster.

What happens if I cross join a table with itself?

You get every row paired with every other row, including itself. If you have a 5-row table and cross join it with itself, you get 25 rows. This is sometimes useful for generating all possible pairs or combinations within a single table, but it is rare.

Will my database crash if I run a large cross join?

Not usually, but it might time out or run very slowly and affect other users. Most databases have safeguards to prevent runaway queries. If you are worried, test on a small subset of data first, or add a LIMIT clause to see the first few rows before running the full query.

How do I know if I need a cross join or a different join?

Ask yourself: do I want every combination of rows from both tables, or do I want only rows where a specific column matches? If you want every combination, use a cross join. If you want matching rows, use an INNER JOIN, LEFT JOIN, or RIGHT JOIN with an ON clause that specifies the match condition.

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.