CROSS JOIN

So far, we have joined tables using matching columns. Now we will explore CROSS JOIN, which works very differently from other joins.

CROSS JOIN creates a Cartesian product — every row from one table is combined with every row from another table.

What is a CROSS JOIN?

A CROSS JOIN returns:

• All possible combinations of rows
• No matching condition is required
• Row count = rows in table A × rows in table B

This makes CROSS JOIN very powerful — and very dangerous if misused.

CROSS JOIN Syntax

SELECT columns
FROM table1
CROSS JOIN table2;
  

There is no ON condition in a CROSS JOIN.

Example Tables

products

+----+------------+
| id | product    |
+----+------------+
| 1  | Laptop     |
| 2  | Phone      |
+----+------------+
  

colors

+----+----------+
| id | color    |
+----+----------+
| 1  | Black    |
| 2  | Silver   |
| 3  | Gold     |
+----+----------+
  

CROSS JOIN Example

Combine each product with every color.

SELECT p.product, c.color
FROM products p
CROSS JOIN colors c;
  

Result:

• Laptop – Black
• Laptop – Silver
• Laptop – Gold
• Phone – Black
• Phone – Silver
• Phone – Gold

2 products × 3 colors = 6 rows.

Implicit CROSS JOIN (Old Style)

This query also produces a CROSS JOIN:

SELECT p.product, c.color
FROM products p, colors c;
  

This style is not recommended because it is less explicit.

When Should You Use CROSS JOIN?

CROSS JOIN is useful in specific scenarios:

• Generating combinations (sizes × colors)
• Creating test or sample data
• Building calendars or schedules
• Matrix-style reporting

When NOT to Use CROSS JOIN

Avoid CROSS JOIN when:

• Tables are large (can explode row count)
• You forgot to add a JOIN condition accidentally
• You only want matching records

Accidental CROSS JOINs are a common cause of slow queries and crashes.

CROSS JOIN vs INNER JOIN

Common Beginner Mistakes

• Forgetting the JOIN condition (causing CROSS JOIN)
• Using CROSS JOIN on large tables
• Not understanding row multiplication

Always double-check your joins before running queries on production databases.

Performance Warning ⚠️

If table A has 10,000 rows and table B has 5,000 rows, a CROSS JOIN will produce:

10,000 × 5,000 = 50,000,000 rows

This can severely impact performance.

What’s Next?

In the next lesson, we will explore SELF JOIN, where a table is joined with itself.