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 after JOIN and what happens depends on the engine: some report a syntax error, but SQLite (the engine behind the code lab) accepts it and runs it as a cross join, so SELECT COUNT(*) FROM Employees e JOIN Departments d; returns 20. Forget it in the comma form and the query runs perfectly on every engine, hands back a wrong answer, and waits for someone downstream to notice. Write the ON every time rather than counting on the parser to catch you.