CROSS JOIN

Objectives

By the end of this lesson, you should be able to:

Produce every combination of rows from two tables with CROSS JOIN
Explain why CROSS JOIN has no ON condition
Recognize when a CROSS JOIN is intentional versus an accidental missing join condition

💡 Why this matters: Generating every size and color combination for a product catalog isn’t about matching related rows, it’s about producing every possible pairing. CROSS JOIN is built for exactly that, and recognizing it also helps catch a genuine mistake: a forgotten ON condition on a different join.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

sql

CREATE TABLE sizes (id SERIAL PRIMARY KEY, size VARCHAR(10) NOT NULL);
CREATE TABLE colors (id SERIAL PRIMARY KEY, color VARCHAR(10) NOT NULL);

INSERT INTO sizes (size) VALUES ('S'), ('M'), ('L');
INSERT INTO colors (color) VALUES ('Red'), ('Blue');

sql

SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c
ORDER BY s.size, c.color;

text

 size | color
------+-------
 L    | Blue
 L    | Red
 M    | Blue
 M    | Red
 S    | Blue
 S    | Red

CROSS JOIN pairs every row in sizes with every row in colors, 3 sizes times 2 colors gives 6 result rows. There’s no ON condition, CROSS JOIN isn’t matching anything, it’s producing the full combination on purpose.

An Accidental CROSS JOIN

Writing FROM sizes, colors (a comma between table names, no JOIN keyword and no ON at all) produces the exact same result as CROSS JOIN, this older syntax is why a missing ON condition on what was meant to be an INNER JOIN is a classic, easy-to-miss bug: instead of an error, PostgreSQL silently produces every combination, a result set far larger than intended, with no warning that a matching condition was forgotten.

When It’s Intentional

CROSS JOIN fits genuine “every combination” needs: generating size/color product variants, building a calendar of every date paired with every store location, or creating test data. Outside of cases like these, seeing CROSS JOIN (or a comma-separated FROM list) in a query is worth a second look, to confirm a matching condition wasn’t simply left out by mistake.

Try It

Write a CROSS JOIN between sizes and colors, and count the rows in the result (should be 6).
Add a third table, materials (with 2 rows), and write a three-way CROSS JOIN between sizes, colors, and materials, predict the row count before running it.
Rewrite the two-table CROSS JOIN using the comma syntax (FROM sizes s, colors c) and confirm the result is identical.
Explain, in your own words, why an accidentally missing ON condition is dangerous specifically because it doesn’t raise an error.

Recap

CROSS JOIN produces every combination of rows from two tables, with no matching condition.
The result size is the product of both tables’ row counts, 3 sizes times 2 colors is 6 rows.
CROSS JOIN is intentional for genuine “every combination” needs, but a missing ON condition on another join type can silently produce the same effect by accident, worth watching for.

Next lesson: SELF JOIN, joining a table to itself.