SQL Interview Prep · Lesson

CROSS JOIN and Cartesian Products

Deliberate cross joins for generating combinations and accidental ones that explode row counts.

CROSS JOIN and Cartesian Products is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Join Interviewers Use to Trap You

A CROSS JOIN pairs every row of the left table with every row of the right table. There is no ON condition. If table A has 4 rows and table B has 3 rows, the result has 4 x 3 = 12 rows.

This is called a Cartesian product. Interviewers ask about it for two reasons: to test whether you can generate combinations on purpose, and to see if you recognize an accidental cross join that explodes a result set.

Explicit CROSS JOIN Syntax

The modern, readable way to write a Cartesian product is the explicit CROSS JOIN keyword. Notice there is no ON clause, which signals intent clearly to a reviewer.

Here we pair every size with every color to build a full product matrix.

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

The Old Comma Syntax

You will still see Cartesian products written with a comma in the FROM clause and no join condition. This is the legacy ANSI syntax.

It produces the exact same result as CROSS JOIN, but it is dangerous: if you meant to add a join condition and forgot, you silently get a cross join instead of an error. Prefer explicit CROSS JOIN.

-- Same result as CROSS JOIN, but easy to write by accident
SELECT s.size, c.color
FROM sizes s, colors c;

A Worked Example: Building a Size Chart

Suppose a shop sells T-shirts in 3 sizes and 4 colors and wants a row for every possible variant, even ones not yet stocked. A cross join generates all 12 combinations in one step.

This is the most common intentional use: producing a complete grid of options.

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

Generating a Calendar Grid

Another classic interview use of CROSS JOIN: build a dense grid so that every combination is present even when data is missing.

Here we cross every store with every date so a later LEFT JOIN to sales gives one row per store per day, filling gaps with zeros. The cross join guarantees no store-day is skipped.

SELECT st.store_id, d.day
FROM stores st
CROSS JOIN calendar d
WHERE d.day BETWEEN DATE '2024-01-01' AND DATE '2024-01-31';

The Accidental Cartesian Explosion

The danger interviewers probe: you join two tables but forget the join condition. Instead of an error, the database happily returns every combination.

If orders has 100,000 rows and customers has 50,000, the result is 5 billion rows. Queries hang, memory blows up, and the numbers look wildly inflated. Recognizing this pattern in a code review is a green flag.

-- BUG: no join condition between the two tables
SELECT o.order_id, c.name
FROM orders o, customers c;
-- returns COUNT(orders) * COUNT(customers) rows

How to Spot an Accidental Cross Join

Symptoms interviewers want you to name:

The row count is roughly the product of the two table sizes, not the sum.
Aggregates like SUM are inflated by a constant multiple.
The query plan shows a Nested Loop with no join filter.

The fix is almost always a missing ON or WHERE equality between the tables.

CROSS JOIN vs INNER JOIN With Always-True ON

A CROSS JOIN is logically identical to an INNER JOIN ... ON 1=1. Both produce the full Cartesian product.

In fact, an inner join is just a cross join followed by a filter on the ON condition. That mental model explains why a missing condition collapses an inner join down into a cross join.

-- These two queries return identical results
SELECT * FROM a CROSS JOIN b;
SELECT * FROM a INNER JOIN b ON 1 = 1;

Cross Joining a Numbers Table

A powerful trick: cross join against a small numbers or generate_series table to multiply rows on purpose. This is used to expand a single row into many, for example turning a quantity into individual unit rows.

Here each product row is repeated qty times by joining to a series and keeping numbers up to the quantity.

SELECT p.product_id, n.n AS unit_number
FROM products p
CROSS JOIN generate_series(1, 100) AS n(n)
WHERE n.n <= p.qty;

Performance and Safe Practice

Because output grows multiplicatively, never cross join two large tables. Keep at least one side tiny (a list of sizes, a small calendar, a numbers table).

In interviews, state this explicitly: 'A CROSS JOIN is safe only when one side is small and bounded; otherwise the result is unmanageable.' That sentence shows judgment, not just syntax knowledge.

When to Reach for CROSS JOIN

Use a deliberate CROSS JOIN when you need every combination of two sets:

Generating option matrices (size x color).
Densifying time series (store x day) before a LEFT JOIN.
Expanding rows via a numbers table.

If you do not want every combination, you almost certainly want a join with an ON condition instead.

Quick Check

Test your understanding of Cartesian products.

Recap: CROSS JOIN

Key takeaways:

A CROSS JOIN pairs every left row with every right row; output size is the product of the inputs.
Comma syntax with no condition produces the same Cartesian product, often by accident.
Use it intentionally for combination grids, time-series densification, and row expansion via a numbers table.
An inner join with a missing ON silently degrades into a cross join, the explosion bug interviewers love.

Frequently asked questions

Is the “CROSS JOIN and Cartesian Products” lesson free?

Yes — the full text of “CROSS JOIN and Cartesian Products” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “CROSS JOIN and Cartesian Products”?

Deliberate cross joins for generating combinations and accidental ones that explode row counts. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “CROSS JOIN and Cartesian Products” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep