10.6 CROSS JOIN

Level: 10 | Version: v1.1 | Author: Meptrasoft

Overview

CROSS JOIN returns every possible combination of rows from two tables.

It does not use an ON condition.

Table A × Table B
       ↓
Every possible combination

If Table A has 2 rows and Table B has 3 rows:

2 × 3 = 6 rows

CROSS JOIN → Every row paired with every row.

Practice Tables

Customers

Orders

Basic CROSS JOIN

SELECT c.name, o.order_id, o.amount
FROM customers_test c
CROSS JOIN orders_test o;

With 2 customers and 2 orders:

2 × 2 = 4 rows

Result:

Notice that the query does not check whether the customer actually placed the order.

CROSS JOIN vs INNER JOIN

INNER JOIN
→ MATCH RELATED ROWS

CROSS JOIN
→ COMBINE EVERY ROW

CROSS JOIN is useful when you genuinely need all combinations, such as generating possible product/size combinations or test scenarios.

Common Beginner Mistake

A CROSS JOIN can produce a very large result.

For example:

1,000 rows × 10,000 rows
= 10,000,000 rows

So it should be used carefully.

CROSS JOIN can cause a result-set explosion.

Placement Quick Points

• CROSS JOIN produces every possible row combination.
• It does not require an ON condition.
• Result size = rows in Table A × rows in Table B.
• It is also called a Cartesian product.
• Large tables can produce extremely large results.

Interview Questions

What is a CROSS JOIN?

A CROSS JOIN returns every possible combination of rows from two tables.

Does CROSS JOIN use ON?

No.

How many rows are produced?

If Table A has m rows and Table B has n rows:

TEXT

m × n

What is a Cartesian product?

It is the result containing every possible combination of rows from two sets/tables.

What is a common risk of CROSS JOIN?

It can produce a very large result set when both tables contain many rows.

Practice & Hands-On Exercises

1. Write a CROSS JOIN between customers_test and orders_test:

SELECT c.name, o.order_id, o.amount
FROM customers_test c
CROSS JOIN orders_test o;

2. Count the number of rows produced.
3. Explain why 2 × 2 produces 4 rows.
4. Explain the difference between CROSS JOIN and INNER JOIN.
5. What would happen with 100 customers and 1,000 products?

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

CROSS JOIN
    ↓
EVERY ROW × EVERY ROW
    ↓
m × n COMBINATIONS

CROSS JOIN creates a Cartesian product by pairing every row from the first table with every row from the second table.

End of CROSS JOIN