0Pricing
SQL Academy · Lesson

Cross Joins and Cartesian Products

Use CROSS JOIN deliberately for combinatorial pairings, and avoid accidental Cartesian explosions from missing join keys.

Cross Joins and Cartesian Products is a free SQL Academy 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 Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Cross Join?

A CROSS JOIN produces every combination of rows: m × n output rows. No ON clause.

SELECT * FROM a CROSS JOIN b;

-- Equivalent:
SELECT * FROM a, b;        -- comma join, no condition

Accidental Cartesian Products

The classic bug: forget the JOIN condition, get every row of A paired with every row of B.

SELECT u.email, o.id
FROM users u, orders o;     -- missing WHERE u.id = o.user_id !
-- m users × n orders rows — millions!

Intended Use Cases

CROSS JOIN is useful when you actually want all pairs:

  • Combinations of categories × time periods for empty-bucket reports
  • Test data: every user × every product
  • Calendars: every store × every date

Calendar × Stores Example

Fill in zero days for every store:

SELECT d.day, s.id, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(CURRENT_DATE - 30, CURRENT_DATE, '1 day') AS d(day)
CROSS JOIN stores s
LEFT JOIN orders o ON o.store_id = s.id
                  AND date_trunc('day', o.created_at) = d.day
GROUP BY d.day, s.id
ORDER BY d.day, s.id;

Cartesian Products and EXPLAIN

An unintended cartesian shows up as a huge row count in EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT u.email, o.id FROM users u, orders o;
-- Nested Loop  (rows=10000000)  ← warning sign

LATERAL: Conditional Cross-Join

LATERAL JOIN is like a CROSS JOIN where the right side can reference the left — covered next lesson.

Permutations and Combinations

For pairs (A,B) without duplicates, add a comparison in the ON:

SELECT a.id, b.id
FROM products a
JOIN products b ON a.id < b.id;
-- All unique pairs of products, no (A,A), no (B,A) duplicates of (A,B)

CROSS JOIN with VALUES

Inline a small set:

SELECT u.email, c.code
FROM users u
CROSS JOIN (VALUES ('EUR'), ('USD'), ('GBP')) AS c(code);

Avoiding Comma-Join Syntax

The implicit comma join (FROM a, b) is legal but hides intent. Prefer explicit CROSS JOIN:

-- Don't (looks like a typo):
FROM users, orders

-- Do:
FROM users CROSS JOIN orders

Performance

CROSS JOIN itself is cheap — there's no condition to evaluate. The cost is the output row count: 10k × 10k = 100M rows.

Recap

CROSS JOIN = all pairs.

  • Use deliberately for combinations
  • Watch for accidental cartesian products (missing WHERE)
  • Combine with generate_series for gap-filling reports

Quick Check

You wrote SELECT * FROM users u, orders o; with no WHERE. What's the output?

Frequently asked questions

Is the “Cross Joins and Cartesian Products” lesson free?

Yes — the full text of “Cross Joins and Cartesian Products” is free to read here on the web, and the SQL Academy 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 Academy course, upgrade to CoddyKit PRO.

What will I learn in “Cross Joins and Cartesian Products”?

Use CROSS JOIN deliberately for combinatorial pairings, and avoid accidental Cartesian explosions from missing join keys. You practise SQL Academy 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 Academy?

No prior experience is required. SQL Academy 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 Joins 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 Academy lesson?

Yes. Every SQL Academy 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

  1. Cross Joins and Cartesian Products
  2. Lateral Joins (LATERAL JOIN)
  3. Anti-Joins and Semi-Joins (NOT EXISTS)
  4. Multi-Table Join Performance Tuning
← Back to SQL Academy