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

A cross join is a database operation that pairs each row from one table with each row from a second table. If the first table has 10 rows and the second has 5 rows, the result contains 50 rows — every possible combination. It does not check whether the rows match on any condition; it straightforward creates all pairings.

The term "cross" refers to the mathematical concept of a Cartesian product, which is the set of all ordered pairs from two sets. In practical database work, cross joins are less common than other join types because they often produce very large result sets, but they serve specific purposes when you actually need every combination.

Key Takeaways

  • A cross join pairs every row from one table with every row from another table, creating a result that contains all possible combinations.
  • The output size grows quickly: a 100-row table crossed with a 50-row table produces 5,000 rows in the result.
  • Cross joins do not use a matching condition (no "ON" clause), unlike inner joins or left joins that require rows to match on specific columns.
  • Common uses include generating all possible combinations for scheduling, pricing matrices, or test data scenarios.

How a cross join differs from other join types

An inner join returns only rows where data in both tables matches a condition you specify — for example, matching customer IDs. A left join keeps all rows from the first table and adds matching data from the second, filling in blanks where no match exists. A cross join ignores matching entirely and straightforward creates every possible pairing.

Because cross joins do not filter based on a condition, they have no "ON" clause in the SQL syntax. You write SELECT * FROM table1 CROSS JOIN table2 rather than SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id. The absence of that matching condition is what makes a cross join fundamentally different.

When cross joins actually get used

Cross joins appear in real work when you need to generate all possible combinations. A restaurant might cross join a table of menu items with a table of portion sizes to create every valid item-size pairing for their ordering system. A scheduling system might cross join a table of employees with a table of time slots to generate all possible shift assignments before filtering down to what actually works.

They also show up in pricing and discount scenarios. If you have a table of products and a table of discount codes, a cross join creates every product-discount pair so you can calculate what each combination costs. Test data generation is another common use: crossing a small set of test values with another set produces a larger matrix of test cases to run.

Why cross joins can create performance problems

The main risk with cross joins is unintended size explosion. If you accidentally cross join a table with 1 million rows against another table with 1,000 rows, you get a result with 1 billion rows. Your database may run out of memory trying to hold that result, or the operation may take hours to complete. This is why cross joins are usually intentional and deliberate rather than accidental.

In practice, you often use a cross join as a starting point and then filter the result down. You might cross join employees and time slots to get all possible shifts, then remove the ones that violate scheduling rules. The cross join itself is fast; the filtering afterward is where the real work happens.

Cross join syntax in common databases

SQL syntax for a cross join is straightforward and consistent across most database systems. The explicit syntax is SELECT * FROM table1 CROSS JOIN table2. Some databases also accept SELECT * FROM table1, table2 (a comma between table names), which produces the same result, though the explicit CROSS JOIN syntax is clearer about your intent.

PostgreSQL, MySQL, SQL Server, and SQLite all support cross joins with the same basic syntax. The difference between databases usually appears in how they optimize the operation internally, not in how you write it. If you are working with large tables, the database's query optimizer may reorder operations to avoid creating the full cross join result in memory.

Cross joins versus other ways to generate combinations

Some databases offer alternative approaches to generating combinations. Window functions and recursive queries can sometimes produce similar results more efficiently, depending on what you are trying to build. However, when you genuinely need every pairing with no filtering, a cross join is usually the most direct and readable approach.

The choice between a cross join and alternatives depends on your specific data and what you plan to do with the result. If you need all combinations and will filter later, a cross join is straightforward. If you only need specific combinations based on a rule, building that rule into the query from the start may be faster.

Frequently Asked Questions

Is a cross join the same as a Cartesian product?

Yes. In mathematics, a Cartesian product is the set of all ordered pairs from two sets. A cross join in SQL is the database implementation of that concept. The terms are used interchangeably in database contexts.

Can I use a WHERE clause to filter a cross join result?

Yes. You can write SELECT * FROM table1 CROSS JOIN table2 WHERE condition to filter the result after the cross join is created. This is functionally equivalent to an inner join with a matching condition, though the syntax and intent are different.

What happens if I cross join a table with itself?

You get every row paired with every row, including each row paired with itself. A 10-row table crossed with itself produces 100 rows. This is sometimes useful for finding all pairs or combinations within a single table.

Why would I use a cross join instead of a WHERE clause?

A cross join makes your intent explicit: you are generating all combinations. Using a comma between table names and a WHERE clause works the same way but is less clear to someone reading the code later. Explicit syntax helps other people understand what you meant to do.

Can a cross join run on very large tables?

Technically yes, but it is usually a bad idea. Crossing a million-row table with a thousand-row table produces a billion-row result. Most databases will either run out of memory or take an extremely long time. If you need combinations from large tables, you usually want to filter or limit the input first.