DocsJoinsCROSS JOIN

CROSS JOIN

Joins

Concept

CROSS JOIN produces the Cartesian product of two tables: every row from the first table paired with every row from the second. If table A has 100 rows and table B has 50, the result has 5,000 rows. There is no ON clause because every possible combination is returned.

While CROSS JOIN can produce massive result sets, it has legitimate uses. It is commonly used to generate combinations — for example, pairing every product with every store location to create a price matrix, or crossing a calendar table with a list of metrics to ensure every date has a row.

CROSS JOIN is also useful with small lookup tables. If you have a table of status codes (5 rows) and want to count occurrences of each status per month, you can CROSS JOIN the status table with a month series and then LEFT JOIN the actual data, ensuring zero-count rows appear in your report.

Syntax

-- Explicit cross join
SELECT p.name, s.size_label
FROM products p
CROSS JOIN sizes s;

-- Implicit cross join (comma syntax — same result)
SELECT p.name, s.size_label
FROM products p, sizes s;

-- Cross join with a VALUES list
SELECT p.name, v.color
FROM products p
CROSS JOIN (VALUES ('Red'), ('Blue'), ('Green')) AS v(color);

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Generate a matrix of all products and all months in 2024
-- for a sales report that shows zero where there are no sales
SELECT
  p.product_id,
  p.name,
  months.m AS report_month,
  COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM products p
CROSS JOIN generate_series(
  '2024-01-01'::date,
  '2024-12-01'::date,
  '1 month'::interval
) AS months(m)
LEFT JOIN order_items oi
  ON oi.product_id = p.product_id
LEFT JOIN orders o
  ON o.order_id = oi.order_id
  AND DATE_TRUNC('month', o.order_date) = months.m
GROUP BY p.product_id, p.name, months.m
ORDER BY p.name, months.m;

Common Pitfalls & Tips

1CROSS JOIN with two large tables produces an enormous result. A 10,000-row table crossed with another 10,000-row table yields 100 million rows.
2An accidental CROSS JOIN (forgetting the ON clause in an INNER JOIN) is one of the most common SQL bugs. Always verify your join conditions.
3The comma-separated FROM syntax (FROM a, b) is an implicit CROSS JOIN. Be careful when listing multiple tables — add WHERE conditions to avoid unintended Cartesian products.

Practice CROSS JOIN queries