14  Relational data

Objectives

• Recognize relational data and keys. A relational dataset organizes information across multiple tables; each table has a primary key that uniquely identifies each row, and a foreign key links a row in one table to a row in another. Learn to identify keys, verify their uniqueness, and create a surrogate key with row_number() when no natural key exists.
• Use mutating joins to combine tables. Mutating joins (left_join(), inner_join(), right_join(), full_join()) add new columns from a matching table by matching rows on keys. Understand how a left join keeps every row of the left table, and how duplicates arise in a many-to-many join.
• Specify join keys explicitly with join_by(). The default join behavior uses every common variable name as the key, which is convenient right up until two tables happen to share a column name that means two different things. Learn to specify keys manually, including keys with different names on each side.
• Use filtering joins to filter rows. Filtering joins (semi_join(), anti_join()) keep or drop rows in one table based on whether a match exists in another; unlike mutating joins, they never add columns and never duplicate rows.
• Avoid pitfalls with joins. Recognize issues such as missing matches, duplicate keys, many-to-many relationships that inflate row counts, and the surprising way a NA in a join key is treated.

Notes

library(tidyverse)
library(nycflights13)

What is relational data?

Data rarely lives in a single flat table. When information naturally splits into multiple tables, you have relational data. The nycflights13 package is a good example: separate tables describe individual flights, airlines, planes, airports, and weather. To connect them, each table has a primary key, a variable (or set of variables) that uniquely identifies each row, and other tables contain foreign keys that reference those primary keys. Keys let you work with smaller, more coherent tables instead of duplicating the same information across many columns.

To reliably join tables, check that a candidate key is actually unique within its table, using count() and filter(n > 1) to surface any duplicates. When no single column is unique on its own, a compound key made of several columns together, such as airport and hour for weather observations, can serve the same purpose. If a table genuinely has no natural key at all, row_number() creates a simple surrogate key, useful mainly for referring to a specific row unambiguously (in a bug report, for instance) rather than for anything relational.

Mutating joins

A mutating join combines variables from two tables by matching rows on their keys, and the four common mutating joins differ only in which rows survive.

• Inner join (inner_join(x, y)): keeps only rows where the key appears in both x and y.
• Left join (left_join(x, y)): keeps every row of x, filling unmatched rows’ new columns with NA. This is the most common join, since it augments your existing data without silently dropping any of it.
• Right join (right_join(x, y)): keeps every row of y, matching as many rows of x as possible.
• Full join (full_join(x, y)): keeps every row from both tables, filling NA wherever there’s no match.

flights |> left_join(airlines, by = "carrier")

# A tibble: 336,776 × 20
    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
   <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
 1  2013     1     1      517            515         2      830            819
 2  2013     1     1      533            529         4      850            830
 3  2013     1     1      542            540         2      923            850
 4  2013     1     1      544            545        -1     1004           1022
 5  2013     1     1      554            600        -6      812            837
 6  2013     1     1      554            558        -4      740            728
 7  2013     1     1      555            600        -5      913            854
 8  2013     1     1      557            600        -3      709            723
 9  2013     1     1      557            600        -3      838            846
10  2013     1     1      558            600        -2      753            745
# ℹ 336,766 more rows
# ℹ 12 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
#   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#   hour <dbl>, minute <dbl>, time_hour <dttm>, name <chr>

Because a mutating join adds columns the same way mutate() does, it’s easy not to notice new columns arriving if the table already has many; select() down to the relevant keys and columns before joining if you want to see exactly what changed.

Specifying join keys

By default, dplyr joins on every variable name the two tables have in common, which is convenient exactly until two tables share a column name that doesn’t mean the same thing in both places (see Example 13.1). join_by() lets you name the key (or keys) explicitly, including an equi join where the matching columns have different names on each side:

flights |> left_join(airports, join_by(dest == faa))

# A tibble: 336,776 × 26
    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
   <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
 1  2013     1     1      517            515         2      830            819
 2  2013     1     1      533            529         4      850            830
 3  2013     1     1      542            540         2      923            850
 4  2013     1     1      544            545        -1     1004           1022
 5  2013     1     1      554            600        -6      812            837
 6  2013     1     1      554            558        -4      740            728
 7  2013     1     1      555            600        -5      913            854
 8  2013     1     1      557            600        -3      709            723
 9  2013     1     1      557            600        -3      838            846
10  2013     1     1      558            600        -2      753            745
# ℹ 336,766 more rows
# ℹ 18 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
#   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#   hour <dbl>, minute <dbl>, time_hour <dttm>, name <chr>, lat <dbl>,
#   lon <dbl>, alt <dbl>, tz <dbl>, dst <chr>, tzone <chr>

The older syntax by = "carrier" or by = c("playerID" = "id") still works and does the same thing; join_by() is simply the more explicit, more readable modern spelling, and the one you’ll need for the non-equi joins mentioned below.

Filtering joins

A filtering join keeps or drops rows from the first table based solely on whether a match exists in the second, and, unlike a mutating join, never adds a single column and never duplicates a row (see Example 13.3).

• Semi join (semi_join(x, y)): keeps rows in x that have at least one match in y. Use it to restrict x to cases that also appear somewhere in another table.
• Anti join (anti_join(x, y)): keeps rows in x that have no match in y. Use it to find observations in x that are missing from y, exactly the technique previewed for spotting implicit missing values back in Session 13.

# flights whose plane has no recorded details in the planes table
flights |> anti_join(planes, by = "tailnum") |> distinct(tailnum) |> head()

# A tibble: 6 × 1
  tailnum
  <chr>  
1 N3ALAA 
2 N3DUAA 
3 N542MQ 
4 N730MQ 
5 N9EAMQ 
6 N532UA 

Handling missing matches and duplicate keys

Missing matches are inevitable in real data. Left-joining plane characteristics onto flight records leaves some planes’ details unknown, filled with NA, and it’s worth deciding whether that reflects data that genuinely doesn’t exist or a foreign key that’s simply wrong somewhere upstream.

Duplicate keys are the bigger risk, since a join multiplies rows whenever a key isn’t unique on at least one side (see Example 13.2). Check for duplicates before joining, and decide whether you actually want every combination (setting relationship = "many-to-many" to say so explicitly) or whether one table needs to be summarized down to one row per key first.

A glance at more advanced joins

Every join so far has matched rows on exact equality. dplyr’s join_by() also supports non-equi joins: inequality conditions (join_by(x >= y)) for matching ranges, cross_join() for every possible pairing between two tables, rolling joins (join_by(closest(x <= y))) for matching each row to its nearest counterpart rather than an exact one, and overlap joins (join_by(between(x, y_lower, y_upper)) and similar) for matching a value against a range. These come up most often when matching events to time windows or matching on approximate rather than exact criteria; the four joins covered above handle the large majority of everyday relational work.

Fringe cases and common pitfalls

ExampleExample 13.1

The default join can silently match on a shared column name that means two different things.

Both flights and planes happen to have a column called year, but in flights it means “year the flight occurred” (every value is 2013), while in planes it means “year the plane was manufactured” (ranging from 1956 to 2013). Joining on every shared column name by default pulls year in as part of the key without asking:

small_flights <- flights |> select(year, tailnum) |> slice_head(n = 5)

# accidental: joins on tailnum AND year, so it only "matches" a plane
# manufactured in exactly 2013, which is almost never true
small_flights |> left_join(planes, by = c("tailnum", "year")) |> select(tailnum, manufacturer)

# A tibble: 5 × 2
  tailnum manufacturer
  <chr>   <chr>       
1 N14228  <NA>        
2 N24211  <NA>        
3 N619AA  <NA>        
4 N804JB  <NA>        
5 N668DN  <NA>        

# correct: join on tailnum alone
small_flights |> left_join(planes, by = "tailnum") |> select(tailnum, manufacturer)

# A tibble: 5 × 2
  tailnum manufacturer
  <chr>   <chr>       
1 N14228  BOEING      
2 N24211  BOEING      
3 N619AA  BOEING      
4 N804JB  AIRBUS      
5 N668DN  BOEING      

The first join looks like it worked, since it runs without an error or a warning, but nearly every manufacturer comes back NA, because almost no plane was manufactured the same year as any given 2013 flight. join_by() (or an explicit by =) removes the ambiguity entirely by naming exactly which column, or columns, should actually be compared. Whenever two tables share a column name, pause and check whether it means the same thing in both places before letting a natural join use it.

ExampleExample 13.2

An unexpected many-to-many relationship inflates your row count, but at least it warns you.

x <- tibble(id = c(1, 1, 2), val_x = c("a", "b", "c"))
y <- tibble(id = c(1, 1, 2), val_y = c("m", "n", "o"))

left_join(x, y, by = "id")

Warning in left_join(x, y, by = "id"): Detected an unexpected many-to-many relationship between `x` and `y`.
ℹ Row 1 of `x` matches multiple rows in `y`.
ℹ Row 1 of `y` matches multiple rows in `x`.
ℹ If a many-to-many relationship is expected, set `relationship =
  "many-to-many"` to silence this warning.

# A tibble: 5 × 3
     id val_x val_y
  <dbl> <chr> <chr>
1     1 a     m    
2     1 a     n    
3     1 b     m    
4     1 b     n    
5     2 c     o    

id = 1 appears twice in both x and y, so the join produces every combination of the two, four rows for id = 1 alone, growing a 3-row table into a 5-row result. dplyr detects that this many-to-many situation wasn’t declared up front and warns you, which is exactly the kind of warning worth reading rather than dismissing, since a quietly duplicated dataset can throw off every downstream count and average. If the many-to-many relationship is genuinely intended, relationship = "many-to-many" silences the warning; if it isn’t, the fix is almost always to deduplicate one side of the join first.

ExampleExample 13.3

Filtering joins never add columns, even though it’s tempting to expect them to.

names(semi_join(small_flights, planes, by = "tailnum"))

[1] "year"    "tailnum"

The result of semi_join() has exactly the same columns small_flights started with, nothing from planes at all, because a filtering join only ever asks “does a match exist?” and uses the answer to keep or drop rows. If you actually want columns from the second table, you need a mutating join (typically inner_join(), if you also want to drop the rows without a match); reach for semi_join()/anti_join() only when the columns you already have are all you need, and you’re just trying to filter based on another table’s contents.

ExampleExample 13.4

A NA in a join key matches another NA, unlike in SQL.

a <- tibble(key = c(1, NA, 3), val_a = c("x", "y", "z"))
b <- tibble(key = c(1, NA, 4), val_b = c("p", "q", "r"))

inner_join(a, b, by = "key")

# A tibble: 2 × 3
    key val_a val_b
  <dbl> <chr> <chr>
1     1 x     p    
2    NA y     q    

Anyone with a SQL background may expect two NULLs to never match each other, since SQL treats NULL as “unknown,” and two unknowns can’t be asserted equal. dplyr’s joins work differently: they treat NA as an ordinary value for matching purposes, so a row with key = NA in a does match a row with key = NA in b. This is rarely what you actually want if NA represents “missing” rather than a genuine shared category, so filtering out NA keys before joining (filter(!is.na(key))) is worth doing explicitly whenever a missing key showing up as a false match would be a real problem.

Recap

Term	Definition
Primary key	A variable (or set of variables) that uniquely identifies each row in a table.
Foreign key	A variable in one table that refers to a primary key in another table.
Surrogate key	An artificial identifier (typically from row_number()) added when no natural key exists.
Mutating join	Adds columns from a second table by matching keys (inner_join(), left_join(), right_join(), full_join()).
join_by()	Specifies join keys explicitly, including keys with different names on each side or non-equality conditions.
Filtering join	Keeps or drops rows from one table based on whether a match exists in another (semi_join(), anti_join()); never adds columns or duplicates rows.
Many-to-many relationship	When a key isn’t unique on either side of a join, so matching rows are duplicated for every combination; triggers a warning unless declared with relationship = "many-to-many".
NA in a join key	Matches another NA in dplyr’s joins, unlike SQL’s NULL, which never matches another NULL.

Check your understanding

NoteProblems

1. What is the difference between a primary key and a foreign key? What is a surrogate key, and when would you need one?
2. Two tables both have a column called id, but in one table it identifies a customer and in the other it identifies an order. What would happen if you joined them with the default by behavior, and how would join_by() help?
3. A left_join() you expected to simply add a couple of columns instead produces a table with far more rows than you started with, along with a warning. What almost certainly happened?
4. Explain why semi_join(orders, customers, by = "customer_id") cannot be used to add a customer’s name to the orders table, even though both tables share a customer_id column.
5. You join two tables on a key that can be NA in both tables, expecting rows with an unknown key to simply be excluded from the match. What actually happens in dplyr, and how would you get the behavior you expected?

TipSolutions

1. A primary key uniquely identifies each row within its own table; a foreign key is a column in a different table that refers back to that primary key, linking the two tables together. A surrogate key is an artificial identifier, typically created with row_number(), added when a table has no natural column (or combination of columns) that’s already unique; it’s mainly useful for referring to a specific row unambiguously, not for representing any real-world relationship.

2. By default, the join would use id as the key, silently comparing a customer identifier to an order identifier as though they meant the same thing, which would produce meaningless or empty matches without any error. join_by() (or an explicit by =) lets you name the actual keys you want compared, or simply avoid using id as the join key at all if the two columns aren’t meant to be compared.

3. The join key almost certainly isn’t unique on at least one side, creating a many-to-many relationship: every duplicate key on one side gets matched against every duplicate key on the other, multiplying rows far beyond a simple one-to-one or one-to-many join. Checking each table’s key with count() and filter(n > 1) before joining would reveal the duplicates.

4. semi_join() is a filtering join: it only ever keeps or drops rows from the first table based on whether a match exists in the second, and never brings in any columns from the second table at all. To add a customer’s name to the orders table, you need a mutating join, such as left_join(orders, customers, by = "customer_id").

5. dplyr treats NA as an ordinary matchable value in a join key, so rows with NA in both tables’ key columns will match each other, the opposite of SQL’s NULL, which never matches another NULL. To exclude rows with a missing key from matching, filter them out of one or both tables first, for example with filter(!is.na(key)), before joining.