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 in one table with each row in another table. If Table A has 5 rows and Table B has 3 rows, a cross join produces 15 rows — every possible combination. It does not check for matching values between tables the way other joins do. It simply creates all pairs.
This is rarely what you want in practice, but it is useful when you actually need to generate all possible combinations — like pairing every product with every store location, or every time slot with every room in a building.
Key Takeaways
- A cross join produces one output row for every combination of input rows, so a 5-row table crossed with a 3-row table yields 15 rows.
- Unlike inner or left joins, a cross join does not match rows based on a condition — it pairs everything with everything.
- The result grows very quickly: crossing a 100-row table with a 100-row table produces 10,000 rows.
- Cross joins are useful for generating all possible pairings, such as every product in every location or every time slot in every room.
How a cross join differs from other joins
Most joins — inner join, left join, right join — match rows based on a condition. For example, an inner join between a Customers table and an Orders table might match rows where the customer ID is the same. Only matching pairs appear in the result.
A cross join ignores conditions entirely. It produces every possible pairing. In SQL, you write it without an ON clause:
SELECT * FROM TableA CROSS JOIN TableB;
Some databases also accept this syntax, which does the same thing:
SELECT * FROM TableA, TableB;
The second form is older and can be confusing because it looks like a regular join but produces a cross join instead.
When you actually need a cross join
Cross joins solve specific problems where you need all combinations. A retail company might use one to assign every product to every store location, creating a row for each pairing so they can later fill in inventory counts. A hotel might cross join a Rooms table with a TimeSlots table to generate every possible room-and-time combination for a booking system.
Another common use is generating a calendar. If you have a table of years and a table of months, a cross join produces every year-month combination. You can then add days and times to build a complete schedule.
In data analysis, cross joins help you find gaps. If you cross join a list of customers with a list of months, you can see which customer-month pairs have no orders, revealing which customers were inactive in which periods.
The performance cost of cross joins
Cross joins grow in size very quickly. A 10-row table crossed with a 10-row table produces 100 rows. A 100-row table crossed with a 100-row table produces 10,000 rows. A 1,000-row table crossed with a 1,000-row table produces 1 million rows. This happens instantly in the database, but the result can become too large to work with.
If you accidentally write a cross join when you meant to write an inner join — by forgetting the ON condition — you may create millions of rows and slow down your database. Always double-check that you intended a cross join before running it on large tables.
Cross join syntax in common databases
Most databases support the explicit CROSS JOIN syntax:
SELECT * FROM TableA CROSS JOIN TableB;
MySQL, PostgreSQL, SQL Server, and SQLite all accept this. Some older code uses the comma syntax instead:
SELECT * FROM TableA, TableB;
The comma syntax is less clear because it looks like a regular join but produces a cross join. New code should use the explicit CROSS JOIN keyword so the intent is obvious to anyone reading it later.
Cross joins with filters
You can add a WHERE clause to a cross join to filter the results after the pairing is complete. For example, you might cross join Products and Stores, then filter to show only pairings where the product price is above $50 and the store is in a certain region.
This is different from the ON condition in other joins. The WHERE clause runs after all pairs are created, so it can be slower on very large result sets. If you know you want to filter, it is usually better to filter the input tables before the cross join, so fewer rows are paired in the first place.
Frequently Asked Questions
What happens if I cross join a table with itself?
You get every row paired with every other row, including itself. A 5-row table crossed with itself produces 25 rows. This is useful for finding all pairs of items — like every pair of customers who made purchases on the same day — but you usually add a WHERE clause to filter out unwanted combinations.
Can a cross join return zero rows?
Only if one of the input tables is empty. If Table A has 5 rows and Table B has 0 rows, the cross join produces 0 rows because there are no rows in Table B to pair with. Once both tables have at least one row, the cross join will produce output.
Is a cross join the same as a Cartesian product?
Yes. In mathematics and database theory, a Cartesian product is the set of all ordered pairs from two sets. A cross join is the SQL implementation of a Cartesian product.
Why would I use a cross join instead of just listing all combinations manually?
Because manual listing does not scale. If you add a new product or a new store, you would have to manually add dozens of rows. A cross join automatically generates all combinations whenever the input tables change, so your result stays current without extra work.