SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 4 of 18 · ~2 min

Cross Joins and the Cartesian Product

Here's a confession about slide one's history that the last chapter left out: the INNER JOIN dropping Finance wasn't actually the first way this report went wrong. Before anyone got as far as choosing a join type on purpose, the very first draft of the headcount query — written in a hurry, the night before the first Monday meeting — looked like this:

-- Draft zero of slide one, before anyone was thinking about join types at all
SELECT e.name, d.dept
FROM Employees e, Departments d;

Comma-separated tables in FROM, no ON, no WHERE. It ran without complaint and returned twenty rows — five employees times four departments, every possible pairing — instead of anything resembling a headcount. That's a CROSS JOIN, even though the word CROSS never appears: leaving out the join condition entirely pairs every row on one side with every row on the other, so you get (rows in A) × (rows in B) rows out.

SELECT e.name, d.dept
FROM Employees e
CROSS JOIN Departments d;
-- 5 employees × 4 departments = 20 rows, on purpose this time

Deliberate cross joins do exist — seeding a calendar table with every date in a range crossed against every hour of the day, say, so a scheduling tool has a full grid of slots to start from — but they're the exception, and headcount reporting was never going to be one of the places you'd reach for one on purpose. What actually happens far more often is what you just watched: a CROSS JOIN sneaking in unannounced, because two tables got listed with a comma and nobody added back the condition tying them together. The output doesn't announce itself as broken, either — twenty rows reads as a plausible number for a small report, not an obvious red flag the way an outright error or an empty result would.

That's the case for treating explicit JOIN ... ON as the default and the comma form as something to avoid on principle. Forget the ON clause writing JOIN and the parser stops you cold — there's no query to accidentally run. Forget it in the comma form and the query runs perfectly, hands back a wrong answer, and waits for someone downstream to notice. Only one of those two mistakes announces itself.