CROSS JOIN and joins with no equality condition
A join without an equality condition produces the Cartesian product. It rarely ends in a bill — it usually ends in a failed query after burning a lot of slot time.
What it is
CROSS JOIN, or a JOIN whose ON clause contains only inequalities, pairs every row on the left with every row on the right. Two million-row tables produce a trillion rows.
Why it costs money
- Under Editions this is a slot-time catastrophe, and slot time is what you pay for.
- Under on-demand the input bytes are billed even when the query later fails with "resources exceeded".
- It is easy to write by accident — a comma-separated FROM list is a CROSS JOIN.
Worked example
Expensive
SELECT *
FROM `analytics.orders` o, `analytics.customers` c
WHERE o.created_at > c.signup_dateCheaper
SELECT *
FROM `analytics.orders` o
JOIN `analytics.customers` c
ON o.customer_id = c.customer_id
WHERE o.created_at > c.signup_dateThe arithmetic. With 5M orders and 2M customers the first form materialises up to 10 trillion row pairs before filtering. The second joins on a key and produces 5M.
How to fix it
- 1Give every join an equality condition on a key, then apply inequalities as filters.
- 2CROSS JOIN UNNEST(array_column) is a different thing entirely and is completely fine — it expands an array within a row.
- 3Set a maximum bytes billed limit so an accidental Cartesian product fails cheaply.
The analyzer detects this one. Paste your query into the BigQuery Query Cost Analyzer and it will point at the exact line.
Fixing one query is satisfying. Fixing the pattern is the win.
Finitizer finds every instance of this across your BigQuery job history, ranks them by what they actually cost, and keeps checking after you have fixed them.