A joined table can contain more rows than the original even when no new observation was collected. This workshop joins three fictional item quantities to a small zone lookup. One key has two competing lookup entries, another has no match, and one lookup entry is unused. You will audit all three conditions before deciding whether the joined total is meaningful.
Documentation boundary
SQLite’s SELECT documentation describes a left join as retaining unmatched left-side rows with NULL values for missing right-side columns. The Python code below independently illustrates that row pattern with None. It is not a general SQL engine and assumes the fixture’s non-null text keys.
State the intended relationship
The left table contains quantities A two, B three and C zero. The right lookup contains A north, B south, B west and D east. The intended relationship is one zone per item, so the two B lookup rows violate that expectation. Do not select a zone merely because it occurs first. The source fixture provides no evidence that south is more authoritative than west.
| Joined key | Quantity | Zone | Audit meaning |
|---|---|---|---|
| A | 2 | north | One lookup match |
| B | 3 | south | First B match |
| B | 3 | west | Second B match |
| C | 0 | None | Unmatched left key |
Predict the output row count before running the example. A contributes one row, B contributes two and C contributes one unmatched row, giving four. Also list the keys that appear only on the left and only on the right. Those two lists answer different questions: C lacks a lookup match, while D has a lookup entry but no source observation in the left table.
Work the join and audit
left = [("A", 2), ("B", 3), ("C", 0)]
right = [("A", "north"), ("B", "south"), ("B", "west"), ("D", "east")]
joined = []
for key, qty in left:
matches = [zone for right_key, zone in right if right_key == key]
for zone in matches or [None]:
joined.append((key, qty, zone))
assert joined == [("A", 2, "north"), ("B", 3, "south"),
("B", 3, "west"), ("C", 0, None)]
left_keys = {key for key, _ in left}
right_keys = {key for key, _ in right}
assert left_keys - right_keys == {"C"}
assert right_keys - left_keys == {"D"}
assert sum(qty for _, qty in left) == 5
assert sum(qty for _, qty, _ in joined) == 8
assert len(right_keys) < len(right)
print("left3; joined4; unmatched left C; unused right D")The original left quantity total is five. Summing the joined quantity column produces eight because B’s quantity three appears twice. That extra three is a repetition caused by the relationship, not a new item quantity. The table is useful as an audit display but should not be aggregated as if every joined row were a distinct original observation. Row count and key multiplicity must be checked before interpreting the total.
The unmatched C row remains visible with an unknown zone and a genuine quantity zero. Keeping it does not invent a zone. The unused D lookup row is reported separately because the left-oriented join does not create an observation for it. A different join choice could answer another question, but it would need a different stated contract and reconciliation table.
Diagnose two false fixes
Dropping duplicate joined rows would not solve the B problem because the zones differ: the rows are not identical. Averaging B’s two quantities would conceal the lookup disagreement while leaving the source relationship unresolved. Repair the lookup’s authority rule first or quarantine B for review. A mechanical reduction in row count is not proof that the intended one-to-one relationship has been restored.
An inner join would hide C rather than explain it. That might be appropriate for a different explicitly restricted analysis, but it would not satisfy this workshop’s requirement to preserve every left observation. Likewise, assigning east to C because east is unused would invent a relationship. Unmatched-key reports exist to make missing relationships visible, not to encourage arbitrary completion of the table.
Create a cardinality handover
Submit the two inputs, expected relationship, four-row output and two unmatched-key sets. Record the reconciliation: three left observations became four joined rows because one left key matched twice. State that the joined total eight is unsuitable as the original quantity total. The correct original total remains five regardless of how the zone disagreement is eventually resolved.
For an extension, supply an explicitly authoritative corrected lookup containing one B zone, then recompute the join. The output should return to three rows, C should remain unmatched and the quantity total should return to five. The correction is additional scenario evidence, not something inferred from the original fixture. Keep the original audit so a reviewer can see why the lookup needed correction in the first place.
Questions for this exercise
What happens to an unmatched left row?
In the documented left-join pattern it remains, with NULL in missing right-side columns; this illustration represents that absence with None.
Why does the joined total become eight?
The quantity three for B is repeated across its two lookup matches, so it must not be treated as a new observation.
Can I assign the unused zone to C?
No. The fixture gives no evidence linking C to D’s zone. Report the unmatched relationship instead.
Official documentation
Explore the Courses overview or use Contact for general enquiries. This is an original INTIAU editorial self-study exercise; no qualification is awarded.
Related guides
View the study diagram
