SQL CROSS JOIN

A CROSS JOIN combines every row from one table with every row from another table, with no condition linking them. If table A has 3 rows and table B has 4 rows, a CROSS JOIN between them returns 3 × 4 = 12 rows. It’s the only join type that doesn’t ask “how do these rows relate?” — it simply pairs everything with everything, which is exactly why it’s useful for generating combinations, and exactly why it’s dangerous when used by accident.

Overview: How CROSS JOIN Works

In relational algebra, this operation is called the Cartesian product (named after René Descartes, who formalized the idea of pairing elements from two sets). Every row in the left table is combined with every row in the right table, producing a result set whose row count is the product of the two input row counts, and whose column count is the sum of the two input column counts.

Under the hood, the query engine typically implements this as a nested loop: for each row in the outer (left) table, it scans every row in the inner (right) table and emits one combined row per pairing. There’s no join predicate to evaluate, no index lookup to shortcut the work, and no filtering — every possible pairing is included. This makes CROSS JOIN the most expensive join type in terms of output size: joining a 1,000-row table with a 1,000-row table via CROSS JOIN produces 1,000,000 rows.

Compare this to an INNER JOIN ... ON, which is logically equivalent to a CROSS JOIN followed by a WHERE filter that keeps only the rows satisfying the join condition. In fact, older SQL code sometimes writes joins this exact way — listing tables separated by commas in the FROM clause (an implicit CROSS JOIN) and then adding the matching condition in WHERE. Modern style strongly prefers the explicit JOIN ... ON syntax because it separates “how tables relate” from “how to filter rows,” but understanding that an inner join is conceptually a filtered cross join helps demystify how the SQL engine reasons about joins internally.

CROSS JOIN has real, everyday uses: generating every combination of product sizes and colors for a catalog, building a full calendar of days × time slots for a scheduling grid, creating round-robin tournament pairings, or producing test/sample data. It’s the right tool whenever you genuinely want every combination, not a filtered subset.

Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

-- equivalent implicit (comma) syntax:
SELECT columns
FROM table1, table2;

Part	Meaning
CROSS JOIN	Explicit keyword; pairs every row of table1 with every row of table2.
table1, table2	Older, implicit syntax that produces the exact same Cartesian product. Avoid mixing this with other joins in the same query — it reads ambiguously and some engines apply different operator precedence to comma joins versus keyword joins.
No ON / USING	Standard SQL does not attach a join condition to CROSS JOIN, because there’s nothing to match — every row pairs with every row unconditionally. If you need a condition, you want INNER JOIN ... ON instead.
Optional WHERE	You can filter the Cartesian product afterward — this is a legitimate technique for things like self-cross-joins used to generate unique pairs (see Example 2 below).

Examples

Example 1: Generating every size/color combination

CREATE TABLE sizes (size_name TEXT);
INSERT INTO sizes (size_name) VALUES ('Small'), ('Medium'), ('Large');

CREATE TABLE colors (color_name TEXT);
INSERT INTO colors (color_name) VALUES ('Red'), ('Blue');

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

Result:

size_name	color_name
Large	Blue
Large	Red
Medium	Blue
Medium	Red
Small	Blue
Small	Red

There are 3 sizes and 2 colors, so the CROSS JOIN produces 3 × 2 = 6 rows — every possible size/color pairing a clothing catalog would need as a starting point for its SKU list. Note there’s no relationship between sizes and colors; that’s the whole point of a CROSS JOIN.

Example 2: Self CROSS JOIN for round-robin pairings

CREATE TABLE players (player_id INTEGER, player_name TEXT);
INSERT INTO players (player_id, player_name) VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cara');

SELECT p1.player_name AS player_a, p2.player_name AS player_b
FROM players AS p1
CROSS JOIN players AS p2
WHERE p1.player_id < p2.player_id
ORDER BY p1.player_id, p2.player_id;

Result:

player_a	player_b
Ana	Ben
Ana	Cara
Ben	Cara

This crosses the players table with itself (3 × 3 = 9 raw pairings, including a player paired with themselves and every pair counted twice). The WHERE p1.player_id < p2.player_id filter keeps only one direction of each pair and removes self-pairs, leaving exactly the 3 unique matchups needed for a round-robin schedule. This is a genuinely common real-world use of CROSS JOIN: generating all unique combinations of two elements drawn from the same set.

Example 3: Pairing every product with every customer

SELECT product_name, customer_name
FROM products
CROSS JOIN customers;

Result: 9 rows — the Cartesian product of the 3 rows in products and the 3 rows in customers (3 × 3 = 9). Every product appears once alongside each customer, regardless of whether that customer has ever ordered that product. This kind of query is useful as a starting point for something like a marketing matrix (“which products has each customer not yet bought, so we know what to promote to them”), which you’d typically build by CROSS JOINing and then LEFT JOINing against actual order history to find the gaps.

How It Works Step by Step

SQL’s logical processing order applies to CROSS JOIN just like any other join:

• FROM — the engine builds the base row set. For table1 CROSS JOIN table2, this means every row of table1 is combined with every row of table2, with no filtering at all at this stage.
• WHERE — if a WHERE clause follows, it filters the full Cartesian product down to the rows you actually want (as in Example 2, where p1.player_id < p2.player_id trims 9 raw pairings down to 3).
• GROUP BY / HAVING — if present, these operate on the (possibly filtered) cross-joined rows, same as with any other join.
• SELECT — the requested columns are projected from the combined rows.
• ORDER BY / LIMIT — sorting and row limiting happen last, on the final result set.

The key insight: an INNER JOIN ... ON condition is logically just a CROSS JOIN immediately followed by WHERE condition. Engines don’t necessarily execute it that way physically (a real optimizer will use indexes and hash joins to avoid ever materializing the full Cartesian product), but understanding the logical equivalence explains why an accidentally-omitted join condition turns a fast, selective query into a massive, meaningless one.

Common Mistakes

Mistake 1: Forgetting the join condition (accidental CROSS JOIN)

SELECT o.order_id, c.customer_name
FROM orders o, customers c;

This uses the old comma syntax and looks like it might match each order to its customer, but there’s no condition linking o and c at all — it’s a plain CROSS JOIN. With 3 orders and 3 customers, it returns 9 rows, most of which pair an order with a customer who never placed it. This query runs without error, which is exactly what makes it dangerous: nothing warns you that the result is meaningless.

Corrected:

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Adding the explicit JOIN ... ON condition restricts the result to one row per order (3 rows total), each correctly matched to its real customer via customer_id — the relationship a CROSS JOIN alone can never express.

Mistake 2: Attaching a join condition to CROSS JOIN with mismatched columns

SELECT *
FROM employees
CROSS JOIN customers USING (id);

This fails at execution because employees has a column named id, but customers does not (it uses customer_id) — there’s no shared column named id for USING to match on, so the engine raises an error. More fundamentally, this reflects a conceptual mix-up: CROSS JOIN exists precisely for cases where you don’t have a relating column. If you need to match rows on a shared column, use INNER JOIN ... ON (or USING with a column that genuinely exists in both tables) instead of bolting a condition onto CROSS JOIN.

Best Practices

• Use the explicit CROSS JOIN keyword rather than the comma syntax, so anyone reading the query immediately understands a full Cartesian product is intended, not a forgotten join condition.
• Before running a CROSS JOIN, estimate the output size (rows in table A × rows in table B). On large tables this can produce billions of rows and exhaust memory or disk before you even see an error.
• Never mix comma-style joins with explicit JOIN keywords in the same FROM clause — the differing operator precedence across database engines can silently change which tables a condition applies to.
• If you find yourself CROSS JOINing and then filtering heavily with WHERE, consider whether an INNER JOIN ... ON better expresses your intent — it’s clearer and lets the optimizer use indexes instead of materializing every pairing.
• Reach for CROSS JOIN deliberately for combination-generation tasks: catalogs (size × color), schedules (day × time slot), round-robin pairings, or filling gaps in sparse data (e.g., every month × every product, to reveal months with zero sales).
• When self-cross-joining to generate pairs, always add a WHERE id1 < id2 style filter to avoid duplicate reversed pairs and self-pairs.

Practice Exercises

• Exercise 1: Using the sizes and colors tables from Example 1, write a query that returns only the combinations where the color is 'Red'. (Hint: you can filter a CROSS JOIN with WHERE just like any other join.) Expected result: 3 rows, one per size, all paired with Red.
• Exercise 2: Using the shared employees table (4 rows) and products table (3 rows), write a CROSS JOIN that lists every employee alongside every product. How many rows should it return, and why?
• Exercise 3: Take the round-robin query from Example 2 and extend the players table with a 4th player, 'Deja' (player_id 4). Without running it, work out how many unique pairings the same query (with the WHERE p1.player_id < p2.player_id filter) would now return, then verify by running it.

Summary

• CROSS JOIN pairs every row of one table with every row of another, producing a Cartesian product with row count = (rows in A) × (rows in B).
• It takes no ON or USING condition in standard usage — there’s nothing to match, by design.
• An INNER JOIN ... ON condition is logically equivalent to a CROSS JOIN immediately followed by a WHERE condition; optimizers avoid materializing the full product, but the logical order still explains the behavior.
• Legitimate uses include generating combinations (sizes × colors), schedules, round-robin pairings, and filling in sparse data.
• The most common real-world mistake is an accidental CROSS JOIN — a comma-joined FROM clause missing its WHERE condition — which runs without error but returns meaningless, inflated results.
• Prefer the explicit CROSS JOIN keyword over comma syntax, and always sanity-check the expected output row count before running one on large tables.