Joins & Relationships
Inner, left, and the one that silently duplicates all your rows.
45 minDifficulty 2/5databaseAI-writtenWritten by a model on 16 August 2026 and not yet read by a person. Checked automatically: schema, the pedagogical rules the hand-written material is held to, and every diagram parsed for real.
Before this
Why this exists
A report needs each order alongside its customer's name, but orders and customers live in separate tables — a join is how you combine rows from both based on a shared key, in one query, rather than fetching each table separately and stitching them together in application code. Get the join TYPE wrong, though, and you can silently duplicate rows or silently drop ones that should have appeared.
The mental model
Think of a join as answering: 'for each row on one side, what matches on the other side, and what happens when nothing matches?' An INNER join keeps only rows with a match on both sides. A LEFT join keeps every row from the left table regardless of a match, filling in NULLs where the right side has nothing. The difference between them is entirely about what happens to the UNMATCHED rows.
How it works
INNER JOIN discards rows with no match on either side
`orders INNER JOIN customers ON orders.customer_id = customers.id` returns only orders that have a matching customer — an order with a `customer_id` that doesn't exist in the customers table (perhaps deleted) simply DISAPPEARS from the result entirely, with no indication anything was excluded.
LEFT JOIN preserves every row from the left table, matched or not
`customers LEFT JOIN orders ON customers.id = orders.customer_id` returns EVERY customer, even those with zero orders — for a customer with no orders, every column from the `orders` side comes back as NULL. This is the right choice whenever 'show me everyone, including those with no related records' is the actual question being asked.
A one-to-many join duplicates the 'one' side's row, once per match
Joining `customers` to `orders` where one customer has three orders produces THREE rows in the result, each repeating the customer's name and other details identically, once alongside each order. This is correct and expected relational behaviour, but it silently breaks a naive `COUNT(*)` on customers (now inflated by their order count) unless you explicitly account for the duplication.
The join condition, not just the join type, determines correctness
A join condition matching on the wrong columns (or a column that isn't actually unique enough for the intended relationship) produces a result that TECHNICALLY runs without error but silently multiplies or mismatches rows — this is a common source of bugs that don't throw an exception, just quietly wrong numbers in a report nobody double-checks against the source tables.
The mechanism
For each row on the designated 'driving' side of the join, the database looks for rows on the other side satisfying the join condition. An inner join emits a combined row only when a match is found, discarding unmatched rows entirely. A left join emits a combined row for every left-side row regardless — with NULLs filled in for the right side's columns when no match exists — preserving the left table's full row count.
flowchart LR
subgraph Inner [INNER JOIN]
A1[Only matching rows on both sides]
end
subgraph Left [LEFT JOIN]
B1[All left rows] --> B2[NULLs where no right match]
endWhat people get wrong
- INNER JOIN and LEFT JOIN return the same results as long as there happen to be matches for every row.
- That's true only for the specific dataset where every row happens to have a match — the moment ANY row lacks a match (a customer with no orders, say), INNER JOIN silently drops it while LEFT JOIN keeps it with NULLs, which is exactly the case that matters. Testing against data where every relationship happens to be complete can hide this difference until real-world data (which inevitably has some unmatched rows) reveals it as a bug.
- Running COUNT(*) on a joined result gives the count of the 'main' table being queried.
- A one-to-many join multiplies rows on the 'one' side by however many matches exist on the 'many' side — COUNT(*) on the joined result counts these multiplied rows, not the distinct count of the original table, unless explicitly deduplicated. This is one of the most common sources of silently wrong analytics numbers: a report showing '450 customers' that's actually counting order rows, inflated by customers with multiple orders.
- A join that runs without an error is necessarily joining on the correct columns.
- A join condition can be syntactically valid and execute successfully while matching on the WRONG relationship entirely — like joining on a non-unique column that happens to produce plausible-looking but incorrect results, with no error to signal anything went wrong. SQL doesn't verify that a join condition expresses the intended real-world relationship — it only checks that the columns being compared are compatible types, which is a much weaker guarantee than 'this join is correct.'
When not to use it
- You need to know only about entities that have at least one related record — orders that have an associated (still-existing) customer.
- INNER JOIN — its default behaviour of discarding unmatched rows is exactly correct here, not a limitation to work around.
- You need to count distinct entities on the 'one' side of a one-to-many join without inflation from the 'many' side.
- `COUNT(DISTINCT customers.id)` rather than a bare `COUNT(*)`, or restructure the query to aggregate before joining, avoiding the row multiplication entirely.
Terms
- INNER JOIN
- — A join that returns only rows with a match on both sides, discarding unmatched rows from either table entirely.
- LEFT JOIN
- — A join that returns every row from the left table regardless of a match, filling unmatched right-side columns with NULL.
- Join condition
- — The comparison (typically on a foreign key relationship) determining which rows from each table are considered matching for the join.
- Row multiplication
- — The effect of a one-to-many join producing multiple result rows for a single 'one' side row, once per matching row on the 'many' side.
In an interview
A report joining customers to orders shows a higher customer count than the actual number of customers in the database. What's the likely cause?
- the join is one-to-many (a customer can have multiple orders), so each order produces a separate result row repeating that customer's data
- a naive COUNT(*) on the joined result counts these multiplied rows, not distinct customers
- fix: use COUNT(DISTINCT customers.id), or aggregate orders separately before joining
Can you recall it?
Why does an INNER JOIN between orders and customers potentially return fewer orders than actually exist in the orders table?