Four joins, and the question each one answers

Pick the join from the sentence you are going to write

Most people pick a join by habit — left join, because it is the one that usually works. That is backwards. The join is a claim about which rows belong in the answer, and the claim comes from the sentence you intend to publish.

The sentence you will write	The join
“Attendance for every enrolled student”	left, register on the left
“Attendance for students we have both a record and a roster row for”	inner
“Everything from both sides, so nothing is lost”	full
“Students marked present who are not on the roster”	anti

Read those the other way and each join has a failure it invites. An inner join silently discards the rows that did not match, and those rows are frequently the finding. A left join silently multiplies when the right side is not unique. A full join produces a table where a missing value can mean two different things. And an anti-join produces nothing when everything matched, which people read as “the check passed” without checking that it ran.

The arithmetic, once, by hand

Every surprise in this course comes out of one rule. A join emits one row for every pair of rows that share the key.

If a key value appears m times on the left and n times on the right, it contributes m × n rows.

Left occurrences	Right occurrences	Rows emitted
1	1	1
60	1	60
60	2	120
50	3000	150,000

Three consequences fall straight out of that table:

• Row count preserved only when the right side is unique on the key. This is the sentence the whole lesson rests on.
• Row count multiplied when it is not — quietly, with no error, by a factor nobody chose.
• Row count reduced only by an inner join, or by a key that fails to match. A left join never removes a row, so a shrinking left join means the key changed underneath you.

The third line of the table is a real defect from the previous course: two students on the roster twice, sixty school days each, and a left join that adds 120 rows to a 70,245-row table. The fourth is what happens when you join on the wrong column, and we will do it deliberately in a moment.

The setup

Two files. A roster of 1,202 rows covering 1,200 students across 24 schools, and 70,245 daily attendance marks.

import pandas as pd

roster = pd.read_csv("school-roster-2024.v1.csv")
attendance = pd.read_csv("school-attendance-2024.v1.csv", parse_dates=["attendance_date"])

print(len(roster), roster["student_id"].nunique())
print(len(attendance))

library(dplyr)
library(readr)

roster     <- read_csv("school-roster-2024.v1.csv")
attendance <- read_csv("school-attendance-2024.v1.csv")

c(rows = nrow(roster), students = n_distinct(roster$student_id))
nrow(attendance)

1,202 rows and 1,200 students. Deal with that before joining — the previous course showed why, and the rest of this lesson assumes you have.

roster = roster.drop_duplicates(subset=["student_id"], keep="first")

roster <- roster |> distinct(student_id, .keep_all = TRUE)

Keeping the first row is a decision, not a default. For these two students it is the wrong one — one row records the school they left and the other the school they joined, and “first” picks whichever the export happened to sort first. In real work this goes to whoever holds the register. Here it is a placeholder so the joins below have something clean to work on, and it belongs in the cleaning log.

Left join: keep the left table, attach columns

joined = attendance.merge(roster, on="student_id", how="left", validate="many_to_one")
print(len(attendance), "->", len(joined))

joined <- attendance |>
  left_join(roster, by = "student_id", relationship = "many-to-one")

cat(nrow(attendance), "->", nrow(joined), "\n")

70,245 to 70,245. That is what a left join is supposed to do, and the validate / relationship argument is what makes it a guarantee rather than a hope. Without it, the same call on the undeduplicated roster returns 70,365 and tells you nothing.

Inner join: and the rows it takes with it

inner = attendance.merge(roster, on="student_id", how="inner")
print(len(inner), "rows;", len(attendance) - len(inner), "attendance rows dropped")

inner <- attendance |> inner_join(roster, by = "student_id")
cat(nrow(inner), "rows;", nrow(attendance) - nrow(inner), "dropped\n")

Here nothing is dropped, because every attendance row has a roster row. That is unusual and worth saying out loud when it happens.

When it is not zero, the number matters more than the join does. An inner join that drops 4% of a distribution list is dropping four percent of somebody’s beneficiaries, and the report will say “12,000 people reached” with no footnote, because the rows that would have raised the question are the ones that left.

Use an inner join when you have already looked at what it removes. Using it to remove them is how the awkward rows get disposed of without a decision.

Full join: nothing is lost, and now you have two kinds of blank

full = attendance.merge(roster, on="student_id", how="outer", indicator=True)
print(full["_merge"].value_counts())

full <- attendance |> full_join(roster, by = "student_id")

pandas’ indicator=True adds a _merge column reading both, left_only or right_only, and it is the single most useful argument in this lesson. dplyr has no equivalent, so add one:

full <- attendance |>
  mutate(in_attendance = TRUE) |>
  full_join(mutate(roster, in_roster = TRUE), by = "student_id") |>
  mutate(
    side = case_when(
      in_attendance & in_roster ~ "both",
      in_attendance             ~ "attendance only",
      TRUE                      ~ "roster only"
    )
  )

The reason to bother: after a full join, a blank grade means either “this student has no roster row” or “this student has a roster row with no grade recorded”. Those are completely different findings and the join has made them look identical. The side column keeps them apart.

Anti-join: the check, not the join

An anti-join returns the rows on one side with no partner on the other. It is rarely the analysis and almost always the check.

in_roster = set(roster["student_id"])
orphans = attendance[~attendance["student_id"].isin(in_roster)]
never_seen = roster[~roster["student_id"].isin(set(attendance["student_id"]))]

print(len(orphans), "attendance rows with no roster entry")
print(len(never_seen), "enrolled students with no attendance row at all")

orphans     <- attendance |> anti_join(roster, by = "student_id")
never_seen  <- roster |> anti_join(attendance, by = "student_id")

c(orphans = nrow(orphans), never_seen = nrow(never_seen))

Both directions, every time, and they mean opposite things:

• Attendance with no roster row — someone is being recorded who is not enrolled. A data problem, or an enrolment list that is out of date.
• Roster rows with no attendance — enrolled children never marked either way. In an education programme that is not a defect, it is the finding: those are the children who never came.

Both are zero here. Write the check anyway, because next term it will not be.

Joining on the wrong column

Do this once, deliberately, so you recognise the shape of it when it happens by accident. Both files carry school_id after the first join, and joining on it instead of student_id is a single-character mistake:

wrong = attendance.merge(roster, on="school_id", how="left")
print(f"{len(attendance):,} -> {len(wrong):,}")

wrong <- attendance |> left_join(roster, by = "school_id")
format(nrow(wrong), big.mark = ",")

70,245 becomes 3,567,105. Each attendance row matched every student in the same school — about fifty of them — and the attendance rate computed from that table is still around 88%, because multiplying every row by fifty leaves the proportion alone.

That is the point. The rate survives; the counts do not. Anything reported as a number of children rises fiftyfold, and a rate that looks right is exactly the reason nobody checks the count.

What comes next

You have four joins and the arithmetic that governs them. The next lesson turns that into a habit that runs without you thinking about it — a reconciliation table produced by every join, with matched rows, both anti-join counts and an assertion that fails when the row count moves.