Why it matters
Most incorrect reports trace to a join that matched more or fewer rows than the author expected. The syntax is easy; the reasoning is not, because the result depends on key uniqueness, null semantics and where filters are applied. A filter in the WHERE clause on the right side of a left join silently turns it into an inner join, and a duplicate dimension key doubles revenue without any error.
Mental model
A join is a matching exercise between two lists, where each match produces a new row. The metaphor breaks because a list with duplicate keys matches many times, so one entry on the left can become several rows on the right, and nothing warns you.
How it works
- 1State the expected cardinality before writing the join, such as many orders to one customer.
- 2Check that the join key is unique on the side you expect to be unique, with a grouped count.
- 3Choose the join type from the unmatched-row requirement, and put filters on the correct side of an outer join.
- 4Compare row counts before and after the join, and investigate any growth beyond the expected multiplier.
- 5Handle nulls explicitly, since equality predicates never match null keys.
- 6Aggregate after the join only when the grain is understood, or pre-aggregate to the join grain first.
Step 1 of 6
State the expected cardinality before writing the join, such as many orders to one customer.
Figure. The mechanism in this page's own steps. The figure adds sequence, not new claims.
What to notice. A step can be read alone, but the order is the mechanism. The control lets you stop on any one.
Text version
The page's steps: 1. State the expected cardinality before writing the join, such as many orders to one custom… 2. Check that the join key is unique on the side you expect to be unique, with a grouped cou… 3. Choose the join type from the unmatched-row requirement, and put filters on the correct s… 4. Compare row counts before and after the join, and investigate any growth beyond the expec… 5. Handle nulls explicitly, since equality predicates never match null keys. 6. Aggregate after the join only when the grain is understood, or pre-aggregate to the join …
- State the expected cardinality before writing the join, such as many orders to one customer.
- Check that the join key is unique on the side you expect to be unique, with a grouped count.
- Choose the join type from the unmatched-row requirement, and put filters on the correct side of an outer join.
- Compare row counts before and after the join, and investigate any growth beyond the expected multiplier.
- Handle nulls explicitly, since equality predicates never match null keys.
- Aggregate after the join only when the grain is understood, or pre-aggregate to the join grain first.
Trade-offs
| Choice | Good for | Cost | Fails when |
|---|---|---|---|
| Join on a surrogate key | Deterministic and indexed | Requires a lookup or mapping table | The mapping is missing for new source rows |
| Join on a natural key | No mapping needed | Duplicates and case or whitespace mismatches | The source key is not actually unique |
| Pre-aggregate then join | Avoids fan-out | Loses detail needed later | The final query needs row-level attributes |
| Left join with filter in WHERE | Looks like an outer join | Silently becomes an inner join | You needed unmatched rows preserved |
Good for
Deterministic and indexed
Cost
Requires a lookup or mapping table
Fails when
The mapping is missing for new source rows
- The mapping is missing for new source rows
- The source key is not actually unique
- The final query needs row-level attributes
- You needed unmatched rows preserved
Figure. Each choice with what it buys, what it costs, and the case where it loses.
What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.
Text version
Four trade-offs: Join on a surrogate key is good for Deterministic and indexed, costs Requires a lookup or mapping table, and fails when The mapping is missing for new source rows. Join on a natural key is good for No mapping needed, costs Duplicates and case or whitespace mismatches, and fails when The source key is not actually unique. Pre-aggregate then join is good for Avoids fan-out, costs Loses detail needed later, and fails when The final query needs row-level attributes. Left join with filter in WHERE is good for Looks like an outer join, costs Silently becomes an inner join, and fails when You needed unmatched rows preserved
Failure modes
- Fan-out. A duplicate dimension key multiplies fact rows and every sum grows. Debug: compare the joined row count with the fact row count and group the dimension key to find duplicates.
- Accidental inner join. A WHERE filter on the right table removes unmatched left rows. Debug: count nulls in the right key before and after adding the filter.
- Null key loss. Rows with a null foreign key vanish from an inner join and totals fall. Debug: count rows where the key is null in the source table.
In production
Run a cardinality assertion on every model that joins, comparing expected and actual row counts. Keep a uniqueness test on dimension keys, and review join types in code review rather than only in the output.
Common misconceptions
People think: A left join keeps all rows from the left table.
Actually: It does until a WHERE filter on the right table or a later inner join removes them.
People think: Joins are slow, so they should be avoided.
Actually: The cost is usually a missing index or an unnecessary fan-out, not the join itself.
Interview angle
Explain the join by cardinality and unmatched-row behaviour, then name the two ways a left join silently becomes an inner join. Weak answers recite syntax and never check whether the key is unique.
Sources
- PostgreSQL documentation, SELECT — join types and where clauses interact with them.
- PostgreSQL tutorial, joins — worked inner, left, right and full join examples.
- Use The Index, Luke — how join and index choices affect query cost.