SQL CROSS JOIN

When learning SQL joins, most developers start with INNER JOIN, LEFT JOIN, or RIGHT JOIN. But there’s one join that often feels confusing at first:

CROSS JOIN

In this article, we will break down SQL CROSS JOIN in the simplest possible way what it does, how it works, and when (and when not) to use it.

What is a CROSS JOIN in SQL?

A CROSS JOIN returns the Cartesian product of two tables.

That means:

• Every row from the first table is combined with
• Every row from the second table

If:

• Table A has m rows
• Table B has n rows

Then:

• Result will have m × n rows

No matching condition is required.

Simple Definition

CROSS JOIN combines each row of one table with every row of another table.

Syntax of CROSS JOIN

SELECT column_list
FROM table1
CROSS JOIN table2;

There is no ON clause because CROSS JOIN does not compare columns.

Example Scenario

Let’s imagine two simple tables.

Table: Colors

Rows:

• Red
• Blue

Table: Sizes

Rows:

• Small
• Large

CROSS JOIN Query Example

SELECT Colors.color_name, Sizes.size_name
FROM Colors
CROSS JOIN Sizes;

Result Explanation (In Words)

The result will be:

• Red + Small
• Red + Large
• Blue + Small
• Blue + Large

Every color is paired with every size.

How CROSS JOIN Works Internally

Think of it like nested loops:

• Pick the first row from table A
• Pair it with all rows from table B
• Move to the next row of table A
• Repeat

This continues until all combinations are created.

CROSS JOIN vs INNER JOIN

Many beginners confuse these two, so let’s clarify.

INNER JOIN

• Requires a condition
• Returns only matching rows
• Filters data

CROSS JOIN

• No condition required
• Returns all combinations
• Does not filter data

CROSS JOIN = combination generator INNER JOIN = data matcher

When Should You Use CROSS JOIN?

CROSS JOIN is useful when:

• Generating all possible combinations
• Creating test data
• Producing reports with multiple dimensions
• Pairing items like:
• Products × Regions
• Dates × Employees
• Colors × Sizes

When NOT to Use CROSS JOIN

Avoid CROSS JOIN when:

• Tables are large
• You don’t need all combinations
• You accidentally forget a JOIN condition

A CROSS JOIN on big tables can produce millions of rows and hurt performance.

CROSS JOIN Without the Keyword (Important Interview Tip)

This query:

SELECT *
FROM A, B;

Is equivalent to:

SELECT *
FROM A
CROSS JOIN B;

Many developers write a CROSS JOIN accidentally by missing a join condition.

Common Interview Question

Q: What happens if you forget the ON condition in a JOIN?

Answer: SQL performs a CROSS JOIN, producing a Cartesian product.

Key Points to Remember

• CROSS JOIN returns all possible combinations
• No ON or WHERE condition is required
• Rows = rows_in_table1 × rows_in_table2
• Powerful but dangerous if misused
• Mostly used for combinations, not relationships

Exercise CTA

**Practice it yourself.** Solve real SQL exercises and strengthen your query logic.

Interview CTA

**Prepare smarter.** Explore SQL interview questions with clear, practical explanations.

MCQ CTA

**Test your knowledge.** Try quick SQL MCQs and check your understanding instantly.