A join is a filtered product

The Cartesian product is the foundation

Forget “matching” for a moment. The most basic combination of two tables is the Cartesian product (the CROSS JOIN): take every row of users and pair it with every single row of orders. If users has 3 rows and orders has 4, the product has exactly 3 × 4 = 12 rows. No condition, no matching — just every possible pairing.

-- The raw product: every user paired with every order
SELECT u.id AS user_id, o.id AS order_id
FROM users u
CROSS JOIN orders o;
-- 3 users × 4 orders = 12 rows, regardless of who placed what

The row count of a product is |A| × |B| — it multiplies. That single fact is the whole reason a join can explode. Two tables of 100k rows have a product of 10 billion rows. The engine will never materialize all of those if it can help it, but the logical meaning of your query is still “start from that product.” When you see an unexpectedly large result set, the first question to ask is: where did my row count multiply?

An INNER JOIN is that product, filtered

An INNER JOIN ... ON <predicate> is definitionally the Cartesian product with the predicate applied as a filter. These two queries are logically identical:

-- Written as a join (what you'll always write)
SELECT u.id, o.id, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Written as the product + filter (what it MEANS)
SELECT u.id, o.id, o.total
FROM users u
CROSS JOIN orders o
WHERE o.user_id = u.id;

Both ask: of all 12 possible pairings, keep the ones where o.user_id = u.id. The ON clause is not special syntax — it is a filter on the product. The planner never literally builds the 12-row grid (it uses hash or merge or nested-loop strategies to reach the same answer cheaply — that’s the databases track’s join-algorithms lesson), but the result is guaranteed to equal “product, then filter.”

This reframing pays off immediately. The output row count of an inner join is not |A| and not |B|. It is “how many pairs survive the filter,” which depends entirely on the relationship. If every order has exactly one matching user, the result has |orders| rows. If the join key is non-unique on both sides, you can get more rows than either table — a fan-out, the subject of this unit’s last lesson.

Why CROSS JOIN behavior is a feature, not just a footgun

The incident in the hook was an accidental product: someone wrote a join, forgot the ON predicate (or wrote ON true), and got |A| × |B|. SQL doesn’t stop you, because a product is sometimes exactly what you want — a calendar grid, an “every product × every size” combination matrix, filling gaps. The fourth lesson covers deliberate cross joins. The discipline: an ON clause that doesn’t actually constrain the relationship is silently a product, and a product on two large tables is an outage.