Back to the lessonLesson 1 of 8Joins you can prove

Four joins, and the question each one answers

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDF Slides · PowerPoint

Slide 1 / 24
What this lesson covers
- Pick the join from the sentence you are going to write
- The arithmetic, once, by hand
- The setup
- Left join: keep the left table, attach columns
- Inner join: and the rows it takes with it
- Full join: nothing is lost, and now you have two kinds of blank
- Anti-join: the check, not the join
- Joining on the wrong column
- What comes next
Speaker notes
Inner, left, full and anti — chosen from what you need to be true of the result rather than from habit. Plus the row-count arithmetic that explains every fan-out you will ever see.

Slide 2 / 24

Pick the join from the sentence you are going to write

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

Speaker notes

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. 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.

Slide 3 / 24
The arithmetic, once, by hand
Left occurrences Right occurrences Rows emitted
1 1 1
60 1 60
60 2 120
50 3000 150,000
Speaker notes
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.
Slide 4 / 24
The arithmetic, once, by hand
- 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…
Speaker notes
Three consequences fall straight out of that table: 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.

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

Slide 5 / 24

The setup — In Python

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))

Speaker notes

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

Slide 6 / 24

The setup — In R

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)

Slide 7 / 24
The setup — In Python
```
roster = roster.drop_duplicates(subset=["student_id"], keep="first")
```
Speaker notes
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.

Slide 8 / 24

The setup — In R

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

Slide 9 / 24
The setup
- Keeping the first row is a decision, not a default — For these two students it is the wrong one — one row records the…
Speaker notes
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.

Slide 10 / 24

Left join: keep the left table, attach columns — In Python

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

Slide 11 / 24
Left join: keep the left table, attach columns — In R
```
joined <- attendance |>
  left_join(roster, by = "student_id", relationship = "many-to-one")

cat(nrow(attendance), "->", nrow(joined), "\n")
```
Speaker notes
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.

Slide 12 / 24

Inner join: and the rows it takes with it — In Python

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

Slide 13 / 24

Inner join: and the rows it takes with it — In R

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

Slide 14 / 24
Inner join: and the rows it takes with it
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.
Speaker notes
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.

Slide 15 / 24

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

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

Slide 16 / 24
Full join: nothing is lost, and now you have two kinds of blank — In R
```
full <- attendance |> full_join(roster, by = "student_id")
```
Slide 17 / 24
Full join: nothing is lost, and now you have two kinds of blank — In R
```
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"
    )
  )
```
Speaker notes
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: 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.

Slide 18 / 24

Anti-join: the check, not the join — In Python

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")

Speaker notes

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.

Slide 19 / 24

Anti-join: the check, not the join — In R

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))

Slide 20 / 24
Anti-join: the check, not the join
- Attendance with no roster row — someone is being recorded who is not enrolled. A data problem, or an enrolment list…
- Roster rows with no attendance — enrolled children never marked either way. In an education programme that is not a…
Speaker notes
Both directions, every time, and they mean opposite things: Both are zero here. Write the check anyway, because next term it will not be.
Slide 21 / 24
Joining on the wrong column — In Python
```
wrong = attendance.merge(roster, on="school_id", how="left")
print(f"{len(attendance):,} -> {len(wrong):,}")
```
Speaker notes
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:
Slide 22 / 24
Joining on the wrong column — In R
```
wrong <- attendance |> left_join(roster, by = "school_id")
format(nrow(wrong), big.mark = ",")
```
Speaker notes
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.
Slide 23 / 24
What comes next
- You have four joins and the arithmetic that governs them.
Speaker notes
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.
Slide 24 / 24
Where this goes next
Read the full lesson, with runnable code Back to the lesson

What this lesson covers

Pick the join from the sentence you are going to write

The arithmetic, once, by hand

The arithmetic, once, by hand

The setup — In Python

The setup — In R

The setup — In Python

The setup — In R

The setup

Left join: keep the left table, attach columns — In Python

Left join: keep the left table, attach columns — In R

Inner join: and the rows it takes with it — In Python

Inner join: and the rows it takes with it — In R

Inner join: and the rows it takes with it

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

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

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

Anti-join: the check, not the join — In Python

Anti-join: the check, not the join — In R

Anti-join: the check, not the join

Joining on the wrong column — In Python

Joining on the wrong column — In R

What comes next

Where this goes next