What Does CROSS JOIN Produce?

A CROSS JOIN pairs every row of the first table with every row of the second. There is no join condition, so the result is the Cartesian product: rows_left multiplied by rows_right. Three customers and four products give twelve rows, none of them related to each other.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name VARCHAR(60) NOT NULL
);

INSERT INTO products (id, name) VALUES
  (1, 'Mechanical Keyboard'),
  (2, 'USB-C Hub'),
  (3, '27-inch Monitor');

INSERT INTO categories (id, name) VALUES
  (1, 'peripherals'),
  (2, 'displays');

SELECT p.name AS product, c.name AS category
FROM products AS p
CROSS JOIN categories AS c
ORDER BY p.name, c.name;

Six rows come back: the Mechanical Keyboard with both categories, the USB-C Hub with both, and the 27-inch Monitor with both. Every product appears once per category, and the combinations are nonsense — a monitor is not a peripheral — which is exactly why an unqualified cross join is nearly always a mistake.

The row count is simply the product of the two input counts, so the danger is exponential: ten tables of ten rows joined without conditions gives ten billion rows, and the query will either run for a very long time or exhaust memory before anyone notices.

How to tell that you have written one by accident. The comma join without a WHERE clause produces the same thing. And a join whose ON clause is always true — ON 1 = 1, or a predicate that does not involve either table — is a cross join in disguise. If a query suddenly returns far more rows than expected, look for a join condition that references the wrong column, because a join key that matches too many values produces the same symptom as a missing condition.

A cross join is useful when you need every possible pairing of two independent sets. The classic case is a calendar: cross join a table of years with a table of months to produce every month of every year, with no gaps and no special-casing. It is also how you build a complete matrix of every product against every region so you can left join the actual data onto it, turning missing combinations into explicit zeroes. And it is a testing tool — cross joining a small table of inputs against itself is a quick way to see how many combinations your logic has to handle.

For date generation, the trick is to make one side of the cross join an integer sequence and then derive the dates from it. SQLite has no generate_series function, so you build the sequence from a recursive CTE and cross join it with a table of years.

CREATE TABLE years (yr INTEGER PRIMARY KEY);

INSERT INTO years (yr) VALUES (2026), (2027);

WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 12
)
SELECT y.yr AS year,
       n.n AS month_number,
       printf('%04d-%02d-01', y.yr, n.n) AS month_start
FROM years AS y
CROSS JOIN nums AS n
ORDER BY y.yr, n.n;

Twenty-four rows come back: twelve months for 2026 followed by twelve for 2027. The recursive CTE produces the numbers 1 through 12, and the cross join pairs each year with every number, so no month is missing even when there is no data for it.

PostgreSQL has generate_series and MySQL 8 has a recursive CTE with the same shape, so the portable version above is what you reach for when the target database varies. The printf call is SQLite-specific; the standard spelling is to_char or a concatenation with the month zero-padded.

The warning is the same as for any cross join: the row count is the product. A year table with fifty rows and a number table with a thousand gives fifty thousand rows, which is fine, but a cross join of two large tables will not be. Always check the sizes before writing one, and prefer a recursive CTE over a numbers table when the sequence is small.

This is the deliberate use of a cross join: build the complete matrix of combinations, then join the real data onto it so missing pairs become explicit zeroes rather than absent rows.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name VARCHAR(60) NOT NULL
);

CREATE TABLE stock (
  product_id INTEGER NOT NULL,
  category_id INTEGER NOT NULL,
  qty INTEGER NOT NULL
);

INSERT INTO products (id, name) VALUES
  (1, 'Mechanical Keyboard'),
  (2, 'USB-C Hub'),
  (3, '27-inch Monitor');

INSERT INTO categories (id, name) VALUES
  (1, 'peripherals'),
  (2, 'displays');

INSERT INTO stock (product_id, category_id, qty) VALUES
  (1, 1, 24),
  (2, 1, 61),
  (3, 2, 7);

SELECT p.name AS product,
       c.name AS category,
       COALESCE(s.qty, 0) AS qty
FROM products AS p
CROSS JOIN categories AS c
LEFT JOIN stock AS s
       ON s.product_id = p.id AND s.category_id = c.id
ORDER BY p.name, c.name;

Six rows come back. The Mechanical Keyboard has 24 in peripherals and 0 in displays; the USB-C Hub has 61 and 0; the 27-inch Monitor has 0 and 7. Without the cross join the result would only contain the three real pairs, and the report would look like a complete picture when it is really a partial one.

The two-part join condition is the detail that matters. The LEFT JOIN to stock has to be conditioned on both product and category, because otherwise it would match the stock rows on the product alone and each product would pick up its real stock quantity against every category.

This pattern is the backbone of any report that has to show zeroes. The general rule is: build the matrix, then left join the facts onto it, then coalesce the missing values to whatever your report calls for a zero. The alternative — grouping the facts and accepting the gaps — produces a chart with misleading discontinuities and totals that do not add up to the sum of the parts.