Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 125 ยท Joins

CROSS JOIN

Learn how SQL CROSS JOIN pairs every row with every other row, how to generate combinations, calendars and report grids, and how to avoid accidental cartesian products.

Every join you've seen so far had a condition that decided which rows belong together. A CROSS JOIN has none: it pairs every row of the first table with every row of the second. If one table has 3 rows and the other has 4, the result has 3 ร— 4 = 12 rows. Mathematicians call this the cartesian product.

That sounds like a recipe for huge, useless results, and by accident it often is. Used on purpose, though, a cross join is the cleanest way to generate combinations: every size in every color, every product in every store, every category for every month of a report. This lesson shows both sides: how to use it well, and how to spot it when it sneaks into a query you didn't mean to write.

Every row with every row

The clearest example uses two tiny tables. The next query creates them in this run's empty database (no sample data needed) and crosses them:

SQL Sizes times colors

Press Run (or Ctrl + Enter) to see the result here.

Twelve rows: each of the three sizes appears once with each of the four colors. A clothing shop could use exactly this query to create one stock-keeping row per variant of a new T-shirt. Add a fifth color and run again: the result grows to 15 rows.

There is no ON clause, because there is nothing to match. Every combination qualifies.

Three ways to write it

Standard SQL offers two spellings, and SQLite accepts a third:

SQL
-- 1. Explicit, standard, and the clearest
SELECT size, color
FROM sizes
CROSS JOIN colors;

-- 2. Comma-separated tables (the old style, also standard)
SELECT size, color
FROM sizes, colors;

-- 3. JOIN without ON: allowed in SQLite and MySQL,
--    a syntax error in PostgreSQL and SQL Server
SELECT size, color
FROM sizes
JOIN colors;

All three return the same 12 rows. Prefer CROSS JOIN: it tells the reader "yes, I want every combination", while a comma list or a bare JOIN looks like a forgotten condition. You can also get a cartesian product from INNER JOIN ... ON 1 = 1 (a condition that's always true), but that's just a roundabout way to say CROSS JOIN.

Counting rows before you run it

Because the size of a cross join is the product of the inputs, it explodes quickly. Always estimate it first. The company database has 6 departments and 7 projects:

SQL Row count is a product ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

6 ร— 7 = 42. That's harmless, but a cross join of two 10,000-row tables returns 100 million rows, and three 1,000-row tables also produce a billion. Cross joins are meant for small inputs: lookup lists, calendars, a handful of options.

Building a complete report grid

The most common professional use of CROSS JOIN is building the skeleton of a report so that missing combinations show up as zero instead of disappearing. Suppose you want units sold per category per month in the shop database. A plain GROUP BY only returns combinations that actually had sales, so a category with no sales in March simply has no March row. A chart drawn from that result would silently skip the month.

The fix has two steps: cross join every category with every month to get all combinations, then LEFT JOIN the real totals onto that grid. The month list and the totals are small queries in parentheses, called derived tables (see Subqueries):

SQL Category by month grid ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Every category gets exactly four rows (January to April 2025), and the combinations with no sales show 0 thanks to COALESCE. Audio and Home Office sold nothing in April, so their April rows show 0. Without the cross join those two rows wouldn't exist at all. Remove the WHERE line to see the full year: 6 categories ร— 12 months = 72 rows.

Grid first, data second. "Every X for every Y, even when nothing happened" is the signature of a cross join followed by a LEFT JOIN. The same pattern fills in days without visits, stores without sales and students without a grade.

Attaching one row to every row

A cross join with a one-row table doesn't multiply anything: it simply attaches that row's columns to every row of the other table. This is handy for comparing each row with an overall figure, such as the company-wide average salary:

SQL Compare with the average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The subquery returns a single row (the average, about 90,361), so the cross join adds the same avg_salary column to all 36 employees without changing the row count. The four Design employees then compare themselves with it: only Farah Tanaka earns more than average. The same trick is great for parameters: cross join a one-row (SELECT '2025-03-01' AS start_date, '2025-03-31' AS end_date) and every condition can refer to p.start_date instead of repeating literal values.

Generating number and date series

SQL has no built-in loop, but a cross join of a small digits table with itself produces many numbers at once. Ten digits crossed with ten digits give 100 rows, which you can turn into the numbers 0 to 99, and from there into dates:

SQL A calendar from two digit tables

Press Run (or Ctrl + Enter) to see the result here.

This prints the 31 days of March 2025, one per row. Join a third copy of digits for 1,000 numbers. date('2025-03-01', '+5 days') adds days to a date; Date and Time Functions covers the modifiers. In real projects you'll often generate series with a recursive CTE instead, and PostgreSQL has a built-in generate_series() function, but the cross join trick works in every database.

Accidental cross joins

The dangerous cross join is the one you didn't mean to write. With the old comma syntax, the join condition lives in WHERE, and if you forget it, the query still runs:

SQL A forgotten join condition ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The broken query returns 960 rows: each of the 24 pending orders paired with all 40 customers. The fixed one returns 24. The broken version gives no error and no warning, only wrong numbers, and if you were summing order totals, a total 40 times too large.

Warning signs of an accidental cartesian product:

  • The row count is suspiciously close to the product of two table sizes.
  • The same row (one order, one employee) appears many times with different partners.
  • Sums and counts are far larger than common sense says they should be.
  • A table in the FROM list isn't mentioned in any ON or WHERE condition.

Writing explicit JOIN ... ON for real relationships and CROSS JOIN for intended products makes this whole class of bugs easy to see.

Performance and dialect notes

  • Cross joins are evaluated as nested loops over the two inputs: for each row on the left, every row on the right. That's fine for a few hundred combinations and dangerous for millions.
  • SQLite treats CROSS JOIN as an optimizer hint. Its query planner never reorders the two tables of a CROSS JOIN, so the left table is always the outer loop. Experienced SQLite users sometimes write CROSS JOIN deliberately to force a join order. Other databases don't attach that meaning.
  • LATERAL and APPLY are different. PostgreSQL's CROSS JOIN LATERAL (...) and SQL Server's CROSS APPLY (...) run a subquery once per row of the left table, with access to that row's columns. They are closer to the correlated subqueries you'll learn soon than to a plain cartesian product. SQLite has no LATERAL.
SQL
-- PostgreSQL: the three most recent orders of every customer
SELECT c.first_name, recent.id, recent.ordered_at
FROM customers AS c
CROSS JOIN LATERAL (
  SELECT o.id, o.ordered_at
  FROM orders AS o
  WHERE o.customer_id = c.id
  ORDER BY o.ordered_at DESC
  LIMIT 3
) AS recent;

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Table a has 5 rows and table b has 8 rows. How many rows does SELECT * FROM a CROSS JOIN b return?

    A cross join pairs every row with every row: 5 ร— 8 = 40.
  2. 2. Which query is a cartesian product even though it doesn't say CROSS JOIN?

    The comma list joins every order to every customer, and the WHERE clause never links the two tables, so every pending order is paired with every customer.
  3. 3. You need units sold per category for every month, showing 0 when a category sold nothing. Which approach works?

    The cross join creates every category-month combination; the LEFT JOIN keeps combinations without sales, and COALESCE turns their NULL into 0.
  4. 4. What does employees CROSS JOIN (SELECT AVG(salary) AS avg_salary FROM employees) return?

    The subquery has exactly one row, so crossing with it adds its columns to every employee without multiplying the row count.

Summary

  • CROSS JOIN pairs every row of one table with every row of another; the row count is the product of the two sizes.
  • Write CROSS JOIN explicitly; FROM a, b without a linking condition does the same thing but looks like a mistake.
  • Use it to generate combinations: product variants, schedules, number and date series.
  • Cross join a grid of all combinations, then LEFT JOIN real data onto it to show zeros instead of missing rows.
  • Crossing with a one-row result attaches a value (an average, a parameter) to every row.
  • Forgotten join conditions create accidental cartesian products; inflated counts and repeated rows are the warning signs.
  • In SQLite, CROSS JOIN also fixes the join order; PostgreSQL LATERAL and SQL Server APPLY are per-row subqueries, not plain cross joins.

Next: Joining Many Tables, where you'll chain several joins in one query and learn to avoid row multiplication.