CROSS JOIN

Learn how CROSS JOIN produces every possible combination of rows between two tables, with no ON clause required.

Introduction

Every join you have seen so far — INNER, LEFT, RIGHT — relies on an ON clause to decide which rows belong together. CROSS JOIN throws that idea out entirely: it pairs every row from one table with every row from the other, no matching condition needed at all.

This lesson explains what a CROSS JOIN produces, when that is actually useful, and why you have to be careful running one on large tables.

What You Will Learn

What CROSS JOIN does and why it needs no ON clause.
How to compute the Cartesian product of two tables.
A practical use case: generating every combination of product variants.
Why CROSS JOIN can be dangerous on large tables.

What is a CROSS JOIN?

A CROSS JOIN produces the Cartesian product of two tables: every single row from the first table is paired with every single row from the second table. If table A has 4 rows and table B has 3 rows, a CROSS JOIN between them produces 4 x 3 = 12 rows total.

Simple Definition

CROSS JOIN pairs every row of one table with every row of the other.
No ON clause or relationship between the tables is needed.
Result size = number of rows in table A x number of rows in table B.

Basic Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

That is the entire syntax — there is no ON clause because CROSS JOIN does not check for any relationship between rows.

Worked Example

Suppose an online store needs to generate every possible combination of size and color for a new t-shirt product. We have a small "sizes" table and a small "colors" table.

setup.sql

CREATE TABLE sizes (
    size_name VARCHAR(10)
);

CREATE TABLE colors (
    color_name VARCHAR(10)
);

INSERT INTO sizes VALUES ('Small'), ('Medium'), ('Large');
INSERT INTO colors VALUES ('Red'), ('Blue');

cross_join.sql

SELECT sizes.size_name, colors.color_name
FROM sizes
CROSS JOIN colors;

Result (3 sizes x 2 colors = 6 rows)

+-----------+-------------+
| size_name | color_name  |
+-----------+-------------+
| Small     | Red         |
| Small     | Blue        |
| Medium    | Red         |
| Medium    | Blue        |
| Large     | Red         |
| Large     | Blue        |
+-----------+-------------+

Every one of the 3 sizes was paired with every one of the 2 colors, giving us all 6 possible product variant combinations in a single query — exactly what a store would need to seed a product_variants table.

Why This Is Useful

CROSS JOIN is not something you reach for every day, but it solves a specific, recurring problem well: generating every possible combination of two small sets of values.

Product Variants

Generating every size/color (or size/color/material) combination for a product catalog.

Calendar Grids

Pairing every date in a range with every store location, to build a report scaffold.

Test Data

Quickly generating a large number of test rows from a couple of small seed tables.

Report Scaffolding

Ensuring a report shows a row for every category x every month, even before any data exists.

The Danger of Large Tables

Because the result size multiplies, CROSS JOIN can produce an enormous number of rows extremely quickly. Two tables of just 1,000 rows each produce 1,000,000 rows when cross joined. Two tables of 100,000 rows each would produce 10,000,000,000 rows — almost certainly enough to freeze or crash a query.

Warning

Never CROSS JOIN two tables of unknown or large size without thinking it through first. It is easy to accidentally write a CROSS JOIN by forgetting an ON clause in what was meant to be an INNER JOIN — always double-check your join conditions before running a query against real data.

CROSS JOIN vs Other Joins

It helps to see CROSS JOIN next to the joins you already know, side by side.

Join Type	Needs ON clause?	Result size
INNER JOIN	Yes	Only matching pairs
LEFT JOIN	Yes	All left rows + matches
RIGHT JOIN	Yes	All right rows + matches
CROSS JOIN	No	Every possible pair (A x B)

Common Mistakes

Avoid These Mistakes

Accidentally writing a CROSS JOIN by forgetting the ON clause on what should have been an INNER JOIN (older comma-style joins like "FROM a, b" have this exact risk).
Running a CROSS JOIN on two real production tables without checking their row counts first.
Confusing CROSS JOIN with INNER JOIN because both can technically be written without WHERE filters.
Not adding a WHERE clause afterward when you actually wanted only some of the combinations.

Best Practices

Only use CROSS JOIN intentionally, on small, well-understood tables.
Check row counts on both tables before running a CROSS JOIN.
Always write CROSS JOIN explicitly rather than the old comma-separated FROM syntax, so your intent is clear.
Add a WHERE clause afterward if you only need a subset of the full combination.

Frequently Asked Questions

Does CROSS JOIN need an ON clause?

No. CROSS JOIN pairs every row with every other row, so there is no relationship to specify.

Can I filter a CROSS JOIN result?

Yes, add a WHERE clause after it just like any other query, to narrow down the full set of combinations.

Is "FROM a, b" the same as CROSS JOIN?

Yes, older comma-separated FROM syntax produces the same Cartesian product, but writing CROSS JOIN explicitly is clearer and considered better style.

How large can a CROSS JOIN result get?

The result has (rows in table A) x (rows in table B) rows, which can grow extremely large extremely fast — always check table sizes first.

When would I actually need this in a real project?

Generating combinations, like every product variant, every date x location pair, or seeding test data, are the most common real-world uses.

Key Takeaways

CROSS JOIN produces the Cartesian product: every row of table A paired with every row of table B.
No ON clause is used or needed.
Result size = rows in A multiplied by rows in B, which grows extremely fast.
Useful for generating combinations like product variants, calendar grids, or test data.
Large tables must be treated carefully to avoid producing huge, runaway result sets.

Summary

CROSS JOIN is the odd one out among joins — it ignores relationships entirely and pairs every row with every other row. Used intentionally on small tables, it is a quick way to generate every possible combination; used carelessly on large tables, it can generate an unmanageable number of rows.

In this lesson, you learned what a Cartesian product is, generated every size/color combination for a product catalog, and learned why CROSS JOIN needs extra caution on large tables. Next, you will learn SELF JOIN, where a table is joined to itself.

Lesson 50 Completed

You understand what a Cartesian product is.
You can write a CROSS JOIN to generate combinations.
You know why CROSS JOIN is risky on large tables.
You are ready to learn SELF JOIN.

Next Lesson →