Students and exams — two inner joins

01 · BEFORE THE JOIN

The two original tables

`ogrenciler` — students

Five records. Each student number occurs once.

Row	ogrenci_no	ders_kodu	ad
S1	101	MAT101	Ali
S2	102	FIZ101	Zeynep
S3	103	KIM101	Can
S4	104	BIO101	Elif
S5	105	TAR101	Mert

ogrenci_no = student ID · ders_kodu = registered course · ad = name

`sinav_sonuclari` — exams

Seven records. A student number can occur more than once.

Row	ogrenci_no	ders_kodu	tur	notu
E1	101	MAT101	Vize	70
E2	101	MAT101	Final	82
E3	101	FIZ101	Vize	55
E4	102	FIZ101	Final	91
E5	103	MAT101	Vize	78
E6	104	BIO101	Final	88
E7	106	MAT101	Vize	66

tur = exam type (Vize: midterm; Final: final) · notu = score

Show the R code that creates these two tables

library(dplyr)

ogrenciler <- data.frame(
  ogrenci_no = c(101, 102, 103, 104, 105),
  ders_kodu  = c("MAT101", "FIZ101", "KIM101", "BIO101", "TAR101"),
  ad         = c("Ali", "Zeynep", "Can", "Elif", "Mert")
)

sinav_sonuclari <- data.frame(
  ogrenci_no = c(101, 101, 101, 102, 103, 104, 106),
  ders_kodu  = c("MAT101", "MAT101", "FIZ101", "FIZ101",
                 "MAT101", "BIO101", "MAT101"),
  tur        = c("Vize", "Final", "Vize", "Final",
                 "Vize", "Final", "Vize"),
  notu       = c(70, 82, 55, 91, 78, 88, 66)
)

print(ogrenciler)
print(sinav_sonuclari)

02 · FIRST JOIN

Use just `ogrenci_no`

Run inner_join() with a single key:

sonuc_tek <- inner_join(
  ogrenciler, sinav_sonuclari,
  by = "ogrenci_no"
)
print(sonuc_tek)

Result: 6 rows

Row	ogrenci_no	ders_kodu.x	ad	ders_kodu.y	tur	notu
1	101	MAT101	Ali	MAT101	Vize	70
2	101	MAT101	Ali	MAT101	Final	82
3	101	MAT101	Ali	FIZ101	Vize	55
4	102	FIZ101	Zeynep	FIZ101	Final	91
5	103	KIM101	Can	MAT101	Vize	78
6	104	BIO101	Elif	BIO101	Final	88

The two ders_kodu columns become ders_kodu.x (student table) and ders_kodu.y (exam table).

Reading the first output

Student 101 appears three times: the student number matches three exam records, even though one exam has FIZ101 instead of MAT101.

Student 103 appears too: its exam is MAT101, although the student table says KIM101. The course codes are different, but the ID matches.

Students 105 and 106 do not appear: 105 has no exam row; 106 is an exam ID without a student row.

03 · SECOND JOIN

Now use `ogrenci_no` and `ders_kodu`

Change only the join condition:

sonuc_iki <- inner_join(
  ogrenciler, sinav_sonuclari,
  by = c("ogrenci_no", "ders_kodu")
)
print(sonuc_iki)

Result: 4 rows

Row	ogrenci_no	ders_kodu	ad	tur	notu
1	101	MAT101	Ali	Vize	70
2	101	MAT101	Ali	Final	82
3	102	FIZ101	Zeynep	Final	91
4	104	BIO101	Elif	Final	88

When both column names are part of by, the result has a single ders_kodu key column.

Reading the second output

A record now survives only when both student number and course code match. Think of the key as the pair (ogrenci_no, ders_kodu).

101/MAT101 still produces two rows (Vize and Final). Student 101's FIZ101 exam disappears. The 103/MAT101 exam also disappears because student 103 is registered for KIM101.

102/FIZ101 and 104/BIO101 match once each. Student 105 has no exam; exam ID 106 has no student. Neither appears.

04 · COMPARE THE OUTPUTS

What happened to each exam row?

The row IDs E1–E7 refer to the original exam table at the top of the page.

Exam row	ID-only join	Two-key join	Why?
E1 · 101 / MAT101 / Vize	Included	Included	Both values match
E2 · 101 / MAT101 / Final	Included	Included	Both values match
E3 · 101 / FIZ101 / Vize	Included	Excluded	Course code differs
E4 · 102 / FIZ101 / Final	Included	Included	Both values match
E5 · 103 / MAT101 / Vize	Included	Excluded	Course code differs
E6 · 104 / BIO101 / Final	Included	Included	Both values match
E7 · 106 / MAT101 / Vize	Excluded	Excluded	No student 106

Joining on one column

6 rows survive, including two mismatched course codes. Identical student numbers are enough to make a match.

Joining on two columns

4 rows survive. The same exam must also have the correct course code. Two otherwise included rows are excluded.

Three distinctions worth noticing

Repeated matches: one row on the left can match several on the right. Two exams for 101/MAT101 give two output rows, even though student 101 is unique in the left table.

Partial matches: matching ogrenci_no does not guarantee matching ders_kodu. That is exactly why the first result includes 101/FIZ101 and 103/MAT101, while the second does not.

No match: an inner join does not retain left-only or right-only records. The 105 student and 106 exam are absent from both results.

05 · TRY IT YOURSELF

Practice and verify

Before you run the two-key join, can you predict how many records survive? Which students will be absent?

Why does student 101 occupy more than one row, despite having a unique ID in the students table?

What would have to change in the source table for the exam of student 103 to survive the two-key join?

Why are there ders_kodu.x and ders_kodu.y columns in the first result, but only one ders_kodu in the second?

Try anti_join(ogrenciler, sinav_sonuclari, by = c("ogrenci_no", "ders_kodu")) to see which student rows have no complete matching key. Reverse the tables to see unmatched exam rows.

Student practice · unfinished R Instructor · complete R

Show the complete R script and teaching comments

# ferdo Lab | UCL PALS | Week 2 independent extra example
# Instructor's completed R script — fictional data, not official UCL material.
# HTML explanations: https://lab.ferdo.net/r/pals-masterclass/extra-inner-join-two-keys/
#
# Start by reading the two tables. Discuss the join after students have
# inspected the original rows. Install dplyr once if needed:
# install.packages("dplyr")

library(dplyr)

# ---------------------------------------------------------------------------
# 1. STARTING TABLES: five student rows, seven exam rows.
# ---------------------------------------------------------------------------
# ogrenci_no is unique among students, but repeats among exam records.
# Both tables have the SAME names for ogrenci_no and ders_kodu.
ogrenciler <- data.frame(
  ogrenci_no = c(101, 102, 103, 104, 105),
  ders_kodu  = c("MAT101", "FIZ101", "KIM101", "BIO101", "TAR101"),
  ad         = c("Ali", "Zeynep", "Can", "Elif", "Mert")
)

sinav_sonuclari <- data.frame(
  ogrenci_no = c(101, 101, 101, 102, 103, 104, 106),
  ders_kodu  = c("MAT101", "MAT101", "FIZ101", "FIZ101",
                 "MAT101", "BIO101", "MAT101"),
  tur        = c("Vize", "Final", "Vize", "Final",
                 "Vize", "Final", "Vize"),
  notu       = c(70, 82, 55, 91, 78, 88, 66)
)

# NOTE — print() reveals the original tables before any join changes them.
print(ogrenciler)
print(sinav_sonuclari)

# ---------------------------------------------------------------------------
# 2. JOIN ON ONE COLUMN (student number).
# ---------------------------------------------------------------------------
# TASK: Join the two tables using only ogrenci_no; inspect the full result.
# inner_join(x, y, by = "column") keeps matching IDs from both tables.
# Because ders_kodu exists on both sides but is NOT the join key,
# dplyr retains both as ders_kodu.x (students) and ders_kodu.y (exams).
# Students 101/FIZ101 and 103/MAT101 match by student ID, even though
# the registered course differs from the course on the exam row.
sonuc_tek <- inner_join(
  ogrenciler,
  sinav_sonuclari,
  by = "ogrenci_no"
)
print(sonuc_tek)  # 6 rows. Two rows have different .x and .y course codes.

# OPTIONAL CHECK: show only course mismatches admitted by the ID-only join.
uyusmayan_dersler <- filter(sonuc_tek, ders_kodu.x != ders_kodu.y)
print(uyusmayan_dersler)  # Two rows: 101/FIZ101 and 103/MAT101.

# ---------------------------------------------------------------------------
# 3. JOIN ON TWO COLUMNS (student number AND course code).
# ---------------------------------------------------------------------------
# TASK: Join again using the pair of keys.
# by = c("ogrenci_no", "ders_kodu") means BOTH comparisons must be TRUE.
# The common key ders_kodu appears only once in the returned table.
sonuc_iki <- inner_join(
  ogrenciler,
  sinav_sonuclari,
  by = c("ogrenci_no", "ders_kodu")
)
print(sonuc_iki)  # 4 rows: two exams for 101, one for 102, one for 104.

# ---------------------------------------------------------------------------
# 4. READ THE DIFFERENCE: why are some rows repeated or absent?
# ---------------------------------------------------------------------------
# Student 101 is listed once on the left but has two MAT101 exams on the
# right: this produces TWO output rows (a one-to-many match).
# The FIZ101 exam of 101 is excluded by the two-key join.
# Student 103 has a MAT101 exam, while their registered course is KIM101:
# the ID matches, but the (ID, course) pair does not.
# Student 105 has no exam; exam student 106 has no student record.
#
# nrow(x) counts rows. dplyr::n_distinct(x) counts unique student numbers.
print(c(one_key_rows = nrow(sonuc_tek),
        two_key_rows = nrow(sonuc_iki),
        two_key_students = n_distinct(sonuc_iki$ogrenci_no)))

# anti_join returns the rows of its FIRST table with NO matching key pair
# in the SECOND table. Reverse the table order to see the other direction.
print(anti_join(ogrenciler, sinav_sonuclari,
                by = c("ogrenci_no", "ders_kodu")))  # 103 and 105.
print(anti_join(sinav_sonuclari, ogrenciler,
                by = c("ogrenci_no", "ders_kodu")))  # 101/FIZ, 103/MAT, 106/MAT.

# Deterministic assertions for this illustrative dataset.
stopifnot(
  nrow(ogrenciler) == 5L,
  nrow(sinav_sonuclari) == 7L,
  !anyDuplicated(ogrenciler$ogrenci_no),
  nrow(sonuc_tek) == 6L,
  nrow(uyusmayan_dersler) == 2L,
  nrow(sonuc_iki) == 4L,
  n_distinct(sonuc_iki$ogrenci_no) == 3L
)

Independent fictional illustration, not an official UCL Moodle task. This companion adds a separate example; the existing 38-course-task sequence stays unchanged.