CROSS JOIN (Cartesian product)

Concepts covered: Cross Joins

A CROSS JOIN has no ON clause and pairs every row on the left with every row on the right. Two tables of 2 rows give 4 pairs; 12,000 titles and 190 regions give 2,280,000. Nothing is matched or filtered, so the product is the full set of combinations, which is what you want whenever the question is about combinations that should exist rather than rows that already do.

SELECT a.category, b.device_type
FROM products a
CROSS JOIN devices b

Every category meets every device type. The result has 2 times 2 rows, and a third device would make it 6. Think of CROSS JOIN as drawing a grid with one table as the rows and the other as the columns, then listing every cell.

The grid that finds the gaps

Netflix licenses titles per country. The availability table stores one row per title and region that is licensed, and the gaps, where a title has no license, have no row anywhere. No filter on availability can find a row that was never written. Build the full grid of titles and regions with CROSS JOIN, LEFT JOIN availability onto the grid, and keep the grid cells where the LEFT JOIN found nothing.

SELECT
  t.title,
  r.region_code
FROM titles t
CROSS JOIN regions r
LEFT JOIN availability a
  ON a.title_id = t.title_id
  AND a.region_code = r.region_code
WHERE a.title_id IS NULL
ORDER BY t.title, r.region_code

The grid has 9 cells for 3 titles and 3 regions. Six are licensed, and the WHERE keeps the 3 whose LEFT JOIN side is NULL: Lunar Bakery in Brazil, and Steel Dawn in both Japan and the United States. Every zero filling report works this way: a grid of days and stores, LEFT JOIN sales, and a day with no sales shows as a row whose sales columns are NULL, which COALESCE(SUM(s.amount), 0) or COUNT(s.sale_id) turns into 0.

Attaching a total to every row

A subquery that returns exactly 1 row, such as a grand total, can be CROSS JOINed onto a many row table without multiplying anything, because n rows times 1 row is n rows. Each region's plays then sits beside the overall total on the same row, ready for a percentage.

SELECT
  r.region_code,
  r.plays,
  ROUND(100.0 * r.plays / t.total_plays, 1) AS pct_of_total
FROM region_plays r
CROSS JOIN (
  SELECT
    SUM(plays) AS total_plays
  FROM region_plays
) t
ORDER BY pct_of_total DESC

The inner query returns a single row holding 1,000, and the CROSS JOIN pastes that row onto all 3 regions. The United States has 50.0% of the plays, Brazil 30.0% and Japan 20.0%, and the 3 percentages add up to 100 because every region divided by the same total.

Pairing a table with itself

A cross join of a table with itself gives every ordered pair, including each row with itself. For a round robin schedule, or a comparison of every team to every other team, keep each unordered pair once with a.team_id < b.team_id. Four teams give 16 ordered pairs: 4 are self pairs, and the remaining 12 are 6 pairs and their 6 mirror images.

SELECT
  a.name AS home,
  b.name AS away
FROM teams a
CROSS JOIN teams b
WHERE a.team_id < b.team_id
ORDER BY a.team_id, b.team_id

Six fixtures come back, Red against each of the other 3, Blue against Green and Gold, and Green against Gold. Using != instead of < would return 12, with every fixture listed twice.

Accidental products

The same multiplication happens by mistake. An INNER JOIN whose ON condition is always true, such as ON 1 = 1, or a join on a key that holds the same value in every row, is a CROSS JOIN with extra steps. The symptom is a result many times larger than either input and a query that runs for an hour. Before any CROSS JOIN, multiply the row counts; before trusting any report that looks too big, check whether a join key was less unique than you assumed.

Do

• Multiply the row counts before running a CROSS JOIN
• Cross join to a 1 row subquery to attach a total
• Pair CROSS JOIN with LEFT JOIN to find missing combinations
• Use a < b to keep each unordered pair once

Avoid

• Cross join 2 large tables with no filter right after
• Write an ON that is always true and call it an INNER JOIN
• Reach for CROSS JOIN where an equality key exists
• Forget that 1,000 times 1,000 is 1,000,000 rows

A report CROSS JOINs 12,000 titles with 190 regions, LEFT JOINs the availability table, and filters to rows where availability is NULL. How many rows exist before the WHERE runs?

• 12,190 rows, one per title plus one per region, before the filter removes licensed pairs
• 2,280,000 rows, one per title and region pair, because CROSS JOIN multiplies the row counts
• 12,000 rows, one per title, because the LEFT JOIN preserves the left side and regions are columns
• 190 rows, one per region, because the grid is grouped by region before the LEFT JOIN runs

About this lesson

This section is part of the Joins: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.