Why does adding an INNER JOIN to a report sometimes make rows disappear?

seniorshould knowfreq 48%

answer

The join does two jobs, not one
Think about what the ON clause asserts
Enrichment and filtering in one operator
Compare counts before and after each link
Missing lookup rows are removed, not NULL-filled

basics

~20 s

Because an inner join is also a filter. Attaching a lookup table asserts that a matching row exists, so any fact row whose key is missing, orphaned or unmatched is removed from the result — silently, with no error and no NULL placeholder.

solid answer

~60 s

Every inner join you add is simultaneously a lookup and an implicit WHERE EXISTS. Joining orders to currencies to fetch a symbol also says "and the currency must exist in that table". Rows with a NULL foreign key, an orphaned key, or a key that mismatches after trimming or case differences simply vanish. In a chain a JOIN b JOIN c JOIN d, the losses compound: a row must match at every link to survive.

Diagnosis is mechanical. Compare the row count before and after adding the join. If it fell, list the fact rows that have no partner — a NOT EXISTS probe against the lookup — and look at the keys that come back; they usually cluster around one cause. Then make a deliberate choice: fix the reference data if the rows should have matched, or preserve the fact rows with an outer join if a missing lookup is a legitimate state. What you must not do is ship a report whose totals quietly exclude an unknown share of the data.

Every inner join you add is simultaneously a lookup and an implicit `WHERE EXISTS`. Joining `orders` to `currencies` to fetch a symbol also says "and the currency must exist in that table". Rows with a NULL foreign key, an orphaned key, or a key that mismatches after trimming or case differences simply vanish. In a chain `a JOIN b JOIN c JOIN d`, the losses compound: a row must match at every link to survive. Diagnosis is mechanical. Compare the row count before and after adding the join. If it fell, list the fact rows that have no partner — a `NOT EXISTS` probe against the lookup — and look at the keys that come back; they usually cluster around one cause. Then make a deliberate choice: fix the reference data if the rows should have matched, or preserve the fact rows with an outer join if a missing lookup is a legitimate state. What you must not do is ship a report whose totals quietly exclude an unknown share of the data.

code

sql · 8 lines

sql

-- Which orders will the currency join throw away, and why?
SELECT o.currency, COUNT(*) AS lost_rows
FROM orders o
WHERE NOT EXISTS (
        SELECT 1 FROM currencies c WHERE c.code = o.currency
      )
GROUP BY o.currency
ORDER BY lost_rows DESC;

go deeper

for a junior

Remember the core fact: an inner join keeps only rows that find a match, so adding one can shrink your result. Check the row count after you add a join.

for a middle

Explain the concrete causes — NULL keys, orphaned references, formatting mismatches, over-narrow predicates — and how losses compound across a chain of joins.

for a senior

Demonstrate a repeatable diagnosis: quantify the drop, isolate the link, list the unmatched keys, then choose between fixing the reference data, preserving rows with an outer join, or keeping the filter deliberately.

for a principal

Own the prevention: foreign key constraints, load ordering between facts and dimensions, and reports that surface an unmatched bucket rather than silently narrowing the population that leadership reads as complete.

An inner join is a WHERE clause in disguise

Developers reach for a join to add columns: bring in the customer name, the currency symbol, the region label. But the operator does two things, and the second one is easy to forget. INNER JOIN lookup ON … also asserts "and a matching lookup row exists". That assertion is a filter, applied to your fact rows, with no syntax that draws attention to it.

So a query that returned 1,000,000 rows yesterday returns 980,000 today because someone added a join to enrich the output with one extra column. No error, no warning, no NULL-filled row that would betray the gap. Twenty thousand rows are simply not there, and every total computed downstream is short by whatever those rows carried.

Where the unmatched rows come from

NULL keys. A comparison with NULL is UNKNOWN, and UNKNOWN is not TRUE, so a fact row with a NULL foreign key can never match anything. Optional relationships are the usual source: assigned_to, campaign_id, parent_id.

Orphans. The key has a value, but no such row exists in the lookup — reference data loaded on a different schedule, a row deleted without cascading, an import that ran before its dimension.

Keys that look equal but are not. Trailing whitespace, case differences, a code stored as '01' on one side and '1' on the other. To a human reading two spreadsheets these match; to = they do not.

Over-narrow predicates. A join that carries a range or a validity window as well as an id drops rows that fall outside the window — a date before the first rate row starts, a value below the lowest band.

Chains. With four inner joins, a row must find a partner four times. Each link is a separate opportunity to lose rows, and the total shrinkage tells you nothing about which link caused it.

The diagnostic sequence

First, quantify. Count the fact table alone, then the joined query. The difference is the number of rows the join removed — plus or minus any multiplication the join also caused, which is why counting DISTINCT fact keys is more honest than counting rows when the join can fan out.

Second, isolate the link. Add the joins back one at a time and record the count after each. The one that drops the count is the culprit; the others are innocent.

Third, look at the offending keys:

sql

SELECT o.currency, COUNT(*) AS lost_rows
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM currencies c WHERE c.code = o.currency)
GROUP BY o.currency
ORDER BY lost_rows DESC;

The answer is almost never random. You will see one NULL bucket, or one code that was renamed, or a handful of values that differ only in case. That pattern tells you which of the causes above you have.

Then make a decision, not a reflex

There are exactly three honest outcomes.

The data is wrong: fix it upstream. Missing reference rows or orphaned keys are a data-quality defect. Load the lookup rows, repair the keys, and add the foreign key constraint that would have prevented the drift. The query was right; the data was not.

Missing is legitimate: preserve the rows. If "no campaign" or "no manager" is a real business state, the fact rows must survive with an empty label — that is what an outer join is for, and it is the correct operator for an optional relationship. Present the gap explicitly rather than hiding it.

The filter is intended: say so. Sometimes you genuinely want only the rows that have a match. Then the join is the filter you wanted — but write a comment or, better, express the intent as an existence test so the next reader sees a filter where a filter is meant. And if the join exists only to filter and you take no columns from the lookup, an existence test is strictly better anyway: it cannot multiply rows the way a join can.

Guardrails worth building

Make the invariant checkable rather than remembered. Assert the row count survives the pipeline. Add the foreign key constraints that stop orphans appearing. When a report must never lose rows, join outward and surface the unmatched count as a visible measure — a row labelled "unknown currency: 20,000 orders" is a bug report; twenty thousand missing rows are silence.

The interview answer

"Because an inner join filters as well as enriches — it asserts the match exists. I quantify the loss by comparing counts, isolate which link drops rows, list the unmatched keys with NOT EXISTS, and then decide deliberately: fix the reference data, switch to an outer join if missing is a valid state, or keep the filter and make the intent explicit."

## An inner join is a WHERE clause in disguise Developers reach for a join to *add columns*: bring in the customer name, the currency symbol, the region label. But the operator does two things, and the second one is easy to forget. `INNER JOIN lookup ON …` also asserts **"and a matching lookup row exists"**. That assertion is a filter, applied to your fact rows, with no syntax that draws attention to it. So a query that returned 1,000,000 rows yesterday returns 980,000 today because someone added a join to enrich the output with one extra column. No error, no warning, no NULL-filled row that would betray the gap. Twenty thousand rows are simply not there, and every total computed downstream is short by whatever those rows carried. ## Where the unmatched rows come from **NULL keys.** A comparison with NULL is UNKNOWN, and UNKNOWN is not TRUE, so a fact row with a NULL foreign key can never match anything. Optional relationships are the usual source: `assigned_to`, `campaign_id`, `parent_id`. **Orphans.** The key has a value, but no such row exists in the lookup — reference data loaded on a different schedule, a row deleted without cascading, an import that ran before its dimension. **Keys that look equal but are not.** Trailing whitespace, case differences, a code stored as `'01'` on one side and `'1'` on the other. To a human reading two spreadsheets these match; to `=` they do not. **Over-narrow predicates.** A join that carries a range or a validity window as well as an id drops rows that fall outside the window — a date before the first rate row starts, a value below the lowest band. **Chains.** With four inner joins, a row must find a partner four times. Each link is a separate opportunity to lose rows, and the total shrinkage tells you nothing about which link caused it. ## The diagnostic sequence First, **quantify**. Count the fact table alone, then the joined query. The difference is the number of rows the join removed — plus or minus any multiplication the join also caused, which is why counting `DISTINCT` fact keys is more honest than counting rows when the join can fan out. Second, **isolate the link**. Add the joins back one at a time and record the count after each. The one that drops the count is the culprit; the others are innocent. Third, **look at the offending keys**: ```sql SELECT o.currency, COUNT(*) AS lost_rows FROM orders o WHERE NOT EXISTS (SELECT 1 FROM currencies c WHERE c.code = o.currency) GROUP BY o.currency ORDER BY lost_rows DESC; ``` The answer is almost never random. You will see one NULL bucket, or one code that was renamed, or a handful of values that differ only in case. That pattern tells you which of the causes above you have. ## Then make a decision, not a reflex There are exactly three honest outcomes. **The data is wrong: fix it upstream.** Missing reference rows or orphaned keys are a data-quality defect. Load the lookup rows, repair the keys, and add the foreign key constraint that would have prevented the drift. The query was right; the data was not. **Missing is legitimate: preserve the rows.** If "no campaign" or "no manager" is a real business state, the fact rows must survive with an empty label — that is what an outer join is for, and it is the correct operator for an optional relationship. Present the gap explicitly rather than hiding it. **The filter is intended: say so.** Sometimes you genuinely want only the rows that have a match. Then the join *is* the filter you wanted — but write a comment or, better, express the intent as an existence test so the next reader sees a filter where a filter is meant. And if the join exists **only** to filter and you take no columns from the lookup, an existence test is strictly better anyway: it cannot multiply rows the way a join can. ## Guardrails worth building Make the invariant checkable rather than remembered. Assert the row count survives the pipeline. Add the foreign key constraints that stop orphans appearing. When a report must never lose rows, join outward and surface the unmatched count as a visible measure — a row labelled "unknown currency: 20,000 orders" is a bug report; twenty thousand missing rows are silence. ## The interview answer "Because an inner join filters as well as enriches — it asserts the match exists. I quantify the loss by comparing counts, isolate which link drops rows, list the unmatched keys with `NOT EXISTS`, and then decide deliberately: fix the reference data, switch to an outer join if missing is a valid state, or keep the filter and make the intent explicit."

How do you find out exactly which rows the join removed?
Probe the fact table for rows with no partner: `SELECT key, COUNT(*) FROM facts f WHERE NOT EXISTS (SELECT 1 FROM lookup l WHERE l.code = f.key) GROUP BY key`. Group the result by the key value — the losses almost always cluster on a NULL bucket, a renamed code, or a formatting difference, and that pattern names the cause.
The join is there only to filter, and you select no columns from the joined table. Is that a good use of a join?
No. Use an existence test instead. A join that exists purely to assert a match can also multiply rows when the key is not unique on that side, so the row count depends on data you are not even reading. An existence predicate expresses the intent, cannot fan out, and reads as the filter it is.
When is losing those rows the right behaviour?
When a missing match genuinely disqualifies the row — a sale with no valid product should not appear in a product report. The requirement is that the exclusion be deliberate and visible: comment it, or report the excluded count alongside the result, so nobody later reads a short total as the whole population.

saying these in an interview costs you the question

Assumes a join only adds columns and never removes rows
Expects unmatched fact rows to appear with NULL labels
Blames the aggregation when the join dropped the rows
Adds DISTINCT or an outer join without diagnosing the cause
Ships a report without comparing counts before and after

Why does adding an INNER JOIN to a report sometimes make rows disappear?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

An inner join is a WHERE clause in disguise

Where the unmatched rows come from

The diagnostic sequence

Then make a decision, not a reflex

Guardrails worth building

The interview answer

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics4 topics

An inner join is a WHERE clause in disguise

Where the unmatched rows come from

The diagnostic sequence

Then make a decision, not a reflex

Guardrails worth building

The interview answer

answer

go deeper

context