What a Cross Join Does in SQL and When You'd Actually Use It

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

A cross join is a SQL operation that pairs each row in one table with each row in another table. If your first table has 10 rows and your second table has 5 rows, a cross join produces 50 rows — every possible combination. It does not look for matching values between tables the way other joins do. It straightforward creates a result set containing every pairing.

Most SQL operations filter data down. A cross join does the opposite — it expands the result set to include every combination. This makes it useful for specific situations, but dangerous if you run it accidentally on large tables. A cross join of a 1,000-row table and a 1,000-row table produces 1 million rows.

Key Takeaways

A cross join creates one row for every combination of rows from two tables, multiplying the row counts together.
Unlike other joins, a cross join does not match rows based on a condition — it pairs everything with everything.
Cross joins are useful for generating all possible combinations, such as pairing every product with every store location or every date with every employee.
Running a cross join on large tables can produce millions of rows and slow your database, so always verify your table sizes first.

How a cross join differs from other join types

An inner join or left join matches rows based on a condition you specify — usually a shared ID or name. You write something like "match orders to customers where customer_id is the same in both tables." A cross join ignores that condition entirely. It does not care whether the rows have anything in common.

Think of it this way: an inner join asks "which rows belong together?" A cross join asks "what are all the ways I could pair these rows?" If you have a table of three colors (red, blue, green) and a table of two sizes (small, large), an inner join might find nothing if the tables share no matching column. A cross join produces six rows: red-small, red-large, blue-small, blue-large, green-small, green-large.

Real situations where you actually need a cross join

A common use is generating a complete schedule. Suppose you have a table of employees and a table of shifts. A cross join produces every employee-shift pairing, which you can then filter or assign. Another example: you have a list of products and a list of store locations. A cross join shows you every product-location combination, which you might use to check inventory or plan distribution.

Date generation is another practical case. If you have a table of dates and a table of employees, a cross join creates one row for each employee on each date. You can then use this as a base to fill in attendance records, time-off requests, or daily task assignments. The cross join gives you the complete grid; you add the details afterward.

Reporting and analysis also use cross joins. If you need to show every possible combination of product category and sales region, a cross join builds that foundation. You then join in actual sales data to see which combinations have revenue and which do not.

The syntax for writing a cross join

The SQL syntax is straightforward. In most databases, you write:

SELECT * FROM table1 CROSS JOIN table2;

Some databases accept an older syntax:

SELECT * FROM table1, table2;

Both produce the same result. The first is clearer because it explicitly says "cross join." The second looks like a regular join and can confuse readers. Most SQL style guides recommend using the explicit CROSS JOIN keyword so anyone reading the code knows you intended to pair everything with everything.

If you want only certain columns from each table, name them:

SELECT table1.name, table2.location FROM table1 CROSS JOIN table2;

Why cross joins can cause performance problems

The row count multiplies. A cross join of a 100-row table and a 100-row table produces 10,000 rows. A cross join of a 10,000-row table and a 10,000-row table produces 100 million rows. Your database has to create, store, and sort all those rows, which consumes memory and CPU time. On a busy database server, a large cross join can slow down other queries running at the same time.

Always check your table sizes before running a cross join. If you are unsure, add a WHERE clause to limit the result first, or use a LIMIT clause to see just the first few rows. Never run a cross join on production tables without testing it on a copy first.

When you might accidentally write a cross join

The most common mistake is forgetting the join condition. You write an inner join but forget the ON clause:

SELECT * FROM orders INNER JOIN customers;

This produces a cross join because no condition tells the database which orders belong to which customers. The correct version includes the condition:

SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

Some databases will reject the first version and show an error. Others will silently produce a cross join, which is why the result set suddenly becomes huge. If a query returns far more rows than you expected, check whether you included the join condition.

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. In SQL, "cross join" and "Cartesian product" mean the same thing. You will see both terms used in documentation and textbooks.

Can I add a WHERE clause to a cross join?

Yes. You can write SELECT * FROM table1 CROSS JOIN table2 WHERE condition; This creates the full cross join first, then filters the results. However, if you know the condition in advance, it is more efficient to use an INNER JOIN with an ON clause instead, because the database can filter while joining rather than after.

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 table of five employees and you cross join it with itself, you get 25 rows — each employee paired with all five employees. This is sometimes useful for generating comparisons or finding all possible pairs, but it is rarely what you want by accident.

Do all SQL databases support cross join syntax?

Yes. Every major SQL database — PostgreSQL, MySQL, SQL Server, Oracle, SQLite — supports cross joins. The syntax is identical across all of them. Some older databases might only support the comma syntax, but CROSS JOIN works everywhere modern SQL is used.

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.