--- title: 'Join Operations' source: 'https://academia.sh/en/courses/data-analysis-with-python/join-operations' course: 'Data Analysis with Python' language: en updated: '2026-10-07T04:31:10+00:00' license: 'CC BY-SA 4.0' objectives: - 'Counts how many rows the inner, left outer, full outer and cross joins produce from the same two tables' - 'Calculates how many rows and how many cubic meters of excess counting the fan-out produces when the key grain is not verified' - 'Measures the gain of the join in the number of passes: a nested loop against a join built with a key index' - 'Shows with a row calculation that the join type is not a preference but a decision that has to be reported' --- # Join Operations The reading table of 3,199 rows and the visit table of 818 rows are joined on the same key with four modes and four different row counts come out: inner join 1,965, left outer 3,820, full outer 3,856, cross join 2,616,782 rows. The inner join drops 1,855 readings and opens the remaining 1,344 rows into 1,965 rows; this fan-out of 621 rows moves the total from 26,450.81 m³ to 38,701.46 m³, that is, it produces 12,250.65 m³ of excess counting. Although the relationship of the key is many-to-many, it is verified nowhere: the key of 468 rows on the left side corresponds to more than one row on the right, and these 468 rows silently open into 1,089 rows. A hand-written nested loop makes 2,616,782 comparisons, while an indexed join finishes in 4,017 passes. Grouping stays within a single table. Most questions of analysis, however, ask for more than one table: the field visits have to come next to the reading table. The join lesson of the Data Collection and Preparation course did this job with a hand-written matching loop and kept the row calculation item by item; 3,471 readings and 1,329 subscribers gave 3,302 rows, 169 readings did not match, and 69 subscribers never appeared in the output. That calculation is the input of this lesson and is not repeated. In a dataframe interface the same job is a single call, and that call chooses a **join type**. The measure of this lesson is this: the same two tables, the same key, four modes and four different row counts. A second number comes next to it: when the key grain is not verified, how many rows multiply and how much the total inflates. - **DF22. The join type** is the decision that determines the row count of the output. The **inner** join keeps only the matching rows, the **left outer** also keeps the unmatched left rows, the **full outer** also keeps the unmatched ones on both sides. The **cross** join never reads the key and produces all the pairs of the two tables. - **DF23. Key grain** is how many rows a key value corresponds to on each side. The relationship can be one-to-one, one-to-many or many-to-many, and the join does this **without verifying** it. - **DF24. Fan-out** is a left row being repeated as many times as it has matches on the right. Fan-out is not an error; the error is taking a total over a multiplied table. - **DF25.** Even when the output row count is lower than the input, no elimination may have been made: within the same operation some rows may drop while others multiply, and the net change hides both. ## Two Tables and Three Sets ```python # dataset.py — MODEL: the long-format reading table of the previous lesson is # regenerated from the same seed. The dataset is fiction; the detail of the # generator is in the first lesson of the topic. import math M32 = 0xFFFFFFFF SEED, PERIODS = 20260218, ["2026-01", "2026-02", "2026-03"] REGION = [("north", 0.28, 21), ("south", 0.22, 17), ("east", 0.18, 26), ("west", 0.14, 14), ("central", 0.18, 23)] def rng(seed: int): s = ((seed ^ (seed >> 16)) * 2246822507) & M32 s = ((s ^ (s >> 13)) * 3266489909) & M32 s ^= s >> 16 def next_value() -> float: nonlocal s s = (s * 1664525 + 1013904223) & M32 return s / 4294967296 return next_value def pick(u: float, weights: list[float]) -> int: t = 0.0 for i, p in enumerate(weights): t += p if u < t: return i return len(weights) - 1 subscribers, dropped = [], [] for i in range(1400): r = rng(SEED + i) name, _, base = REGION[pick(r(), [b[1] for b in REGION])] r(); r() # the household and satisfaction draws are not used in this lesson (dropped if r() < 0.046 else subscribers).append( {"subscriber_id": 10001 + i, "region": name, "base": base}) valid = {k["subscriber_id"] for k in subscribers} pairs: dict[tuple[int, str], float] = {} for k in subscribers + dropped: r = rng(SEED + 7000 + k["subscriber_id"]) for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])): t = 0.0 if r() < 0.038 else math.floor(k["base"] * math.exp( (r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100 + 0.5) / 100 if k["subscriber_id"] in valid: pairs.setdefault((k["subscriber_id"], PERIODS[d]), t) r() LONG = [{"subscriber_id": a, "period": d, "consumption_m3": t} for (a, d), t in pairs.items()] # Field visits table: one row is one visit and it is not tied to a period. visits = [] for k in subscribers: r = rng(SEED + 31000 + k["subscriber_id"]) for z in range(pick(r(), [0.58, 0.27, 0.11, 0.04])): visits.append({"subscriber_id": k["subscriber_id"], "outcome": ["positive", "repeat", "unreachable"][ pick(r(), [0.60, 0.28, 0.12])]}) readers = {o["subscriber_id"] for o in LONG} visited = {z["subscriber_id"] for z in visits} print(f"reading table {len(LONG)} rows, {len(readers)} subscribers") print(f"visit table {len(visits)} rows, {len(visited)} subscribers") print(f"{len(readers & visited)} subscribers on both sides; only in readings " f"{len(readers - visited)}, only in visits {len(visited - readers)}") ``` ``` reading table 3199 rows, 1260 subscribers visit table 818 rows, 559 subscribers 534 subscribers on both sides; only in readings 726, only in visits 25 ``` Three numbers are known before the join, and these three numbers explain in advance all the row counts that will come out. 534 subscribers are in both tables, 726 subscribers only in the reading table, 25 subscribers only in the visit table. The sum of these three sets is 1,285, and choosing a join type is in fact choosing which of these three sets will be represented in the output. One more detail directly affects the row count: the two tables do not carry the same **grain**. In the reading table a row is one period of a subscriber, in the visit table a row is one visit and it is not tied to a period. When only the subscriber number is used as the key, three readings and two visits of a subscriber produce six rows. This is a property that can be read from the tables themselves and it is known before the join call. ## The Same Two Tables, Four Modes ```python # join_tables.py — the same two tables with four join types. The mode is the # decision that determines the row count of the output. PASSES = {"count": 0} def join_tables(left, right, field: str, mode: str = "inner"): """Puts the two sides next to each other on the key. mode: inner, left_outer, full_outer, cross.""" if mode == "cross": PASSES["count"] += len(left) * len(right) return len(left) * len(right), 0, 0 index: dict = {} for s in right: index.setdefault(s[field], []).append(s) PASSES["count"] += len(right) + len(left) produced, empty_left, matched = 0, 0, set() for l in left: e = index.get(l[field]) if not e: empty_left += 1 produced += 1 if mode in ("left_outer", "full_outer") else 0 continue produced += len(e) matched.add(l[field]) empty_right = len({s[field] for s in right} - matched) if mode == "full_outer": produced += sum(1 for s in right if s[field] not in matched) return produced, empty_left, empty_right WIDTH = [22, 12, 12, 12, 12] show = lambda h: "".join(str(v).rjust(WIDTH[k]) if k else str(v).ljust(WIDTH[0]) for k, v in enumerate(h)) print(show(["join type", "left in", "right in", "output", "unmatched"])) result = {} for mode, name in [("inner", "inner"), ("left_outer", "left outer"), ("full_outer", "full outer"), ("cross", "cross")]: PASSES["count"] = 0 produced, el, er = join_tables(LONG, visits, "subscriber_id", mode) result[mode] = (produced, el, er, PASSES["count"]) print(show([name, len(LONG), len(visits), produced, el if mode != "cross" else 0])) print(f"\ninner join drops {result['inner'][1]} of {len(LONG)} readings and produces " f"{result['inner'][0]} rows") print(f"cross join never reads the key: {len(LONG)} x {len(visits)} = " f"{result['cross'][0]} rows") ``` ``` join type left in right in output unmatched inner 3199 818 1965 1855 left outer 3199 818 3820 1855 full outer 3199 818 3856 1855 cross 3199 818 2616782 0 inner join drops 1855 of 3199 readings and produces 1965 rows cross join never reads the key: 3199 x 818 = 2616782 rows ``` Four modes, the same two tables, four different row counts: 1,965, 3,820, 3,856 and 2,616,782. The ratio between the smallest and the largest is 1,332 times, and the only thing that makes the choice is a mode name that is written or not written in the call. The number of the inner join is interesting in two directions at once. The 1,965 rows that come out are fewer than what went in, and at first sight this looks like an elimination. It is not: 1,855 readings have dropped, and the remaining 1,344 readings have opened into 1,965 rows. Because the net change is negative, the fan-out is not visible inside the number. This is the same behavior that was measured in the Data Collection and Preparation course; what is new here is that the whole operation fits into a single call and that none of the numbers 1,855 and 621 comes back from the call. The left outer join gives 3,820 rows and keeps all the rows of the reading table: 1,965 matched rows and 1,855 with an empty visit side. The full outer, with 3,856 rows, also adds the 36 visit rows of the 25 subscribers who are only in the visit table. The cross join, on the other hand, never reads the key; the 2,616,782 rows it produces are not the answer to a question, they are all the pairs of the two tables. ## Fan-Out and the Key Grain ```python # fan_out.py — if the uniqueness of the key is not verified, the join counts the same # row more than once. The measure is the multiplied rows and the inflated total. counts: dict[int, int] = {} for z in visits: counts[z["subscriber_id"]] = counts.get(z["subscriber_id"], 0) + 1 matched = [o for o in LONG if o["subscriber_id"] in counts] produced = sum(counts[o["subscriber_id"]] for o in matched) total_of = lambda a: round(sum(x["consumption_m3"] for x in a), 2) inflated = round(sum(o["consumption_m3"] * counts[o["subscriber_id"]] for o in matched), 2) print(f"dataset {len(LONG)} rows, {total_of(LONG)} m3") print(f"matched {len(matched)} rows, {total_of(matched)} m3") print(f"inner join output {produced} rows, {inflated} m3; " f"{produced - len(matched)} rows of fan-out, {round(inflated - total_of(matched), 2)} m3 excess counting") def relationship(left, right, field: str) -> str: """The key grain: is it unique on both sides, or is it many on one side.""" left_unique = len({s[field] for s in left}) == len(left) right_unique = len({s[field] for s in right}) == len(right) return ("one-to-one" if left_unique and right_unique else "one-to-many" if right_unique or left_unique else "many-to-many") many = sum(1 for o in matched if counts[o["subscriber_id"]] > 1) print(f"\nkey grain: {relationship(LONG, visits, 'subscriber_id')}; on the left side " f"the key of {many} rows corresponds to more than one row on the right") print(f"when no verification is made, these {many} rows silently open into {produced - len(matched) + many} " f"rows") print(f"\npasses: hand-written nested loop {len(LONG) * len(visits)}, indexed join " f"{len(LONG) + len(visits)}") print(f"rows : the same two tables produce {result['inner'][0]}, {result['left_outer'][0]}, " f"{result['full_outer'][0]} and {result['cross'][0]} rows in four modes") ``` ``` dataset 3199 rows, 63060.63 m3 matched 1344 rows, 26450.81 m3 inner join output 1965 rows, 38701.46 m3; 621 rows of fan-out, 12250.65 m3 excess counting key grain: many-to-many; on the left side the key of 468 rows corresponds to more than one row on the right when no verification is made, these 468 rows silently open into 1089 rows passes: hand-written nested loop 2616782, indexed join 4017 rows : the same two tables produce 1965, 3820, 3856 and 2616782 rows in four modes ``` The price of the fan-out is read on the second line. The total of the 1,344 matched readings is 26,450.81 m³; the total of the same readings in the join output is 38,701.46 m³. The **12,250.65 m³** in between is not new consumption, it is the same readings being counted a second and a third time. The January reading of a subscriber has entered the total twice because that subscriber has two visits. The danger of this number is not in its size but in its **plausibility**. 38,701.46 m³ is smaller than the true total of the dataset, that is, 63,060.63 m³; if an audit looked at "the total does not exceed the dataset total", it would pass this table. Yet the table carries a total that is both undercounted and overcounted: 1,855 readings have not been counted at all, 468 readings have been counted more than once. The last two lines put the gain and the hidden default side by side. The gain is clear: while a hand-written nested loop makes 2,616,782 comparisons, a join that builds a key index on the right side and walks the left side once finishes in 4,017 passes, which is 651 times fewer. The hidden default, on the other hand, is that the relationship is **many-to-many** and that this is verified nowhere. The join call does not ask what the relationship is; had it been asked, it would have been known before the call that 468 rows would open into 1,089 rows. ## Fixing the Key Grain ```python # grain_fix.py — what prevents the fan-out is not the join type but bringing the right side # to the grain: aggregate first, then join. visit_summary = [{"subscriber_id": no, "visit_count": n} for no, n in counts.items()] unique = len({z["subscriber_id"] for z in visit_summary}) == len(visit_summary) print(f"visit table fell from {len(visits)} rows to {len(visit_summary)} rows; " f"is the key unique: {'yes' if unique else 'no'}") print(f"key grain is now {relationship(LONG, visit_summary, 'subscriber_id')}") inner2, empty_left2, empty_right2 = join_tables(LONG, visit_summary, "subscriber_id", "inner") left2, _, _ = join_tables(LONG, visit_summary, "subscriber_id", "left_outer") print(f"\ninner join {inner2} rows (not {result['inner'][0]}), total {total_of(matched)} m3") print(f"left outer join {left2} rows, total {total_of(LONG)} m3: the incoming rows and the incoming " f"total did not change") print(f"fan-out fell from {result['inner'][0] - len(matched)} rows to {inner2 - len(matched)} rows, " f"excess counting from {round(inflated - total_of(matched), 2)} m3 to 0.0 m3") ``` ``` visit table fell from 818 rows to 559 rows; is the key unique: yes key grain is now one-to-many inner join 1344 rows (not 1965), total 26450.81 m3 left outer join 3199 rows, total 63060.63 m3: the incoming rows and the incoming total did not change fan-out fell from 621 rows to 0 rows, excess counting from 12250.65 m3 to 0.0 m3 ``` What removes the fan-out is not changing the join type but bringing the right side to the **grain** of the left side. When the visit table is aggregated per subscriber and falls from 818 rows to 559 rows, the key becomes unique, the relationship stops being many-to-many and returns to one-to-many, and the same join call no longer multiplies any row. The excess counting falls from 12,250.65 m³ to zero, and this fall is not a correction; it never occurred. The last line gives the check that this lesson was looking for. The left outer join made with the right grain closes at 3,199 rows and 63,060.63 m³: the incoming row count and the incoming total have not changed. This is the only cheap sign that a join was done correctly: if the row count and the total of the left table are preserved in the output, the left side has neither dropped nor multiplied. If they are not preserved, the difference is written in two items, and these two items are numbers that the call itself never reports. Aggregating first and joining afterwards also has a price, and it has to be written down: the detail that the 818 visit rows carried, that is, the outcome of each visit, is lost inside the summary. The grain decision therefore depends on the question to be asked. The question "how many visits per subscriber" is answered with the summary table, while the question "how many visits closed as unreachable" is asked of the visit table itself and is never joined with the reading table. ## Summary - The join type determines the row count of the output: the same two tables give 1,965 rows in the inner join, 3,820 in the left outer, 3,856 in the full outer and 2,616,782 in the cross join. - That the inner join gives fewer rows than went in does not mean an elimination: 1,855 readings have dropped, the remaining 1,344 readings have opened into 1,965 rows, and the net change hides both. - Fan-out inflates the total: while the true total of the matched readings is 26,450.81 m³, the total in the output is 38,701.46 m³, and the 12,250.65 m³ in between is readings that have been counted a second time. - Because the inflated total stays below the dataset total, it passes an upper-bound check; the table has undercounting and overcounting at the same time. - The relationship of the key is many-to-many and is not verified: 468 rows silently open into 1,089 rows. The indexed join finishes in 4,017 passes, the nested loop makes 2,616,782 comparisons. ## Next Step The join put the tables next to each other and the row calculation was written down. One axis has not been handled in the dataset yet: time. In the reading table the period is a text column and it can be sorted, but a question between periods such as "how many weeks have passed in between" or "in which week is there no reading at all" cannot be asked in this form. When the time axis is moved to the label index these questions become askable, and a new decision arises: in a resample, is a period that carries no record counted as zero, or counted as never having existed. The next lesson calculates these two answers separately and measures how they change the total and the average.