How a Cross Join Works in SQL and When to 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 the first table has 5 rows and the second has 3 rows, the result contains 15 rows — every possible combination. Unlike other joins that match rows based on a condition (such as matching customer IDs), a cross join creates a result set with no filtering or matching logic.

The basic syntax is straightforward. You write SELECT * FROM table1 CROSS JOIN table2, or the older syntax SELECT * FROM table1, table2. Both produce identical results. The cross join is sometimes called a Cartesian product because it generates all possible pairs.

Key Takeaways

A cross join returns every combination of rows from two tables, multiplying the row counts together to get the result size.
Cross joins have no ON clause because they do not match rows on any condition — they pair everything with everything.
The result set grows very quickly; joining a 100-row table with a 100-row table produces 10,000 rows.
Common uses include generating date ranges, creating all possible product combinations, or building lookup tables for reporting.

How cross joins differ from inner and left joins

An inner join matches rows from two tables based on a condition you specify — for example, matching orders to customers where the customer ID is the same. Only rows that meet the condition appear in the result. A left join keeps all rows from the first table and adds matching rows from the second, filling in NULL values where no match exists.

A cross join ignores any matching condition entirely. It does not care whether rows have anything in common. Every row from the first table pairs with every row from the second, regardless of content. This makes cross joins rare in everyday database work but essential for specific tasks like generating combinations or time-series data.

Real examples of cross join results

Suppose you have a colors table with three rows (red, blue, green) and a sizes table with two rows (small, large). A cross join produces six rows: red-small, red-large, blue-small, blue-large, green-small, green-large. Each color pairs with each size exactly once.

Another example: you have a dates table with 365 rows (one for each day of the year) and a stores table with 10 rows (one for each location). A cross join produces 3,650 rows — one for every combination of date and store. You might use this to build a template for daily sales reporting, ensuring every store has a row for every day, even if no sales occurred.

A third example: you have a products table with 50 items and a warehouses table with 8 locations. A cross join creates 400 rows representing every possible product-warehouse combination. You could then use this to track inventory levels or shipping routes.

When to use a cross join in practice

Cross joins are useful when you need to generate all possible combinations without filtering. Building a calendar is a common case: cross join a years table with a months table to create every year-month combination, then cross join that result with a days table to generate every date in a range. This is faster and cleaner than writing loops in application code.

Another practical use is creating a matrix for reporting. If you want to show sales by product and by region, with every product-region pair represented (even if sales are zero), a cross join builds the skeleton. You then left join actual sales data onto it, filling in the numbers where they exist.

Cross joins also appear in pricing or bundling scenarios. If you sell shirts in five colors and four sizes, a cross join generates all 20 combinations so you can assign prices or SKU numbers to each variant.

Performance considerations and risks

The main risk with cross joins is unintended size explosion. A cross join of two moderately large tables can produce millions of rows in seconds, consuming memory and slowing your database. Always verify the row counts of both tables before writing a cross join. If one table has 1,000 rows and the other has 1,000 rows, the result has 1,000,000 rows.

In most database systems, a cross join is fast because it does not require matching logic or sorting. The database simply iterates through one table and repeats every row from the other. However, the sheer volume of output can overwhelm storage or network bandwidth if you are not careful.

To avoid mistakes, always include a WHERE clause or LIMIT clause after a cross join during testing. Write SELECT * FROM table1 CROSS JOIN table2 LIMIT 10 to see a sample before running the full query. This prevents accidentally generating millions of rows and locking up your database.

Cross join syntax variations

The explicit syntax is SELECT * FROM table1 CROSS JOIN table2. This is clear and readable, and it is the recommended form in modern SQL.

The older comma syntax is SELECT * FROM table1, table2. This also produces a cross join, but it is less obvious to someone reading the code. Many teams avoid it to prevent confusion with inner joins, which also use commas in older SQL dialects.

You can cross join more than two tables. SELECT * FROM table1 CROSS JOIN table2 CROSS JOIN table3 produces every combination of rows across all three. The result size multiplies: if each table has 10 rows, the result has 1,000 rows.

Frequently Asked Questions

What is the difference between a cross join and a full outer join?

A cross join pairs every row from one table with every row from another, with no condition. A full outer join matches rows based on a condition you specify, keeping all rows from both tables and filling in NULL where no match exists. A full outer join typically returns far fewer rows because it filters based on the matching condition.

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 generates all combinations first, then the WHERE clause filters the result. However, if you know the filtering condition in advance, it is often more efficient to use an inner join with that condition instead.

Why would I use a cross join instead of a loop in my application code?

A cross join is typically faster because the database engine is optimized for set operations. Writing nested loops in application code is slower and harder to maintain. A single SQL query is also easier to read and modify than procedural code.

What happens if one of the tables in a cross join is empty?

The result is empty. If either table has zero rows, there are no combinations to create, so the cross join returns no rows. This is useful for validation: if you expect a cross join to produce results and it does not, one of your tables may be empty or filtered incorrectly.

Can I cross join a table with itself?

Yes. SELECT * FROM products CROSS JOIN products pairs every product with every other product, including itself. You would typically use a table alias to distinguish the two copies: SELECT * FROM products p1 CROSS JOIN products p2. This is useful for generating all possible pairs or combinations within a single table.

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.