We have two data sets. Our first data set (superheroes) contains information about fictional characters from graphic novels. There are 7 characters from different publishers. Information about those characters include name, alignment (good or bad), gender (male or female) and the publishers where they belong to.
Our second data set is about publishers and the year they are founded. These two data sets are the perfect minimal example to introduce data table joins because they have a common (key) column and they have both similar and disparate values.
print(superheroes)
# A tibble: 7 × 4
name alignment gender publisher
<chr> <chr> <chr> <chr>
1 Magneto bad male Marvel
2 Storm good female Marvel
3 Mystique bad female Marvel
4 Batman good male DC
5 Joker bad male DC
6 Catwoman bad female DC
7 Hellboy good male Dark Horse Comics
print(publishers)
# A tibble: 3 × 2
publisher yr_founded
<chr> <int>
1 DC 1934
2 Marvel 1939
3 Image 1992
Joins
We call data sets left and right (hence left join, right join), or X and Y (usually X denotes left and Y denotes right). Here we will use superheroes and publishers in different positions but usually superheroes will be on the left.
Left Join
In left join, all rows of X are preserved, only relevant rows Y and multiply rows if there are multiple matchings.
left_join(superheroes, publishers, by ="publisher")
# A tibble: 7 × 5
name alignment gender publisher yr_founded
<chr> <chr> <chr> <chr> <int>
1 Magneto bad male Marvel 1939
2 Storm good female Marvel 1939
3 Mystique bad female Marvel 1939
4 Batman good male DC 1934
5 Joker bad male DC 1934
6 Catwoman bad female DC 1934
7 Hellboy good male Dark Horse Comics NA
Right join is the same as left join but the main data set is Y.
right_join(superheroes, publishers, by ="publisher") %>%arrange(name)
# A tibble: 7 × 5
name alignment gender publisher yr_founded
<chr> <chr> <chr> <chr> <int>
1 Batman good male DC 1934
2 Catwoman bad female DC 1934
3 Joker bad male DC 1934
4 Magneto bad male Marvel 1939
5 Mystique bad female Marvel 1939
6 Storm good female Marvel 1939
7 <NA> <NA> <NA> Image 1992
left_join(publishers, superheroes, by ="publisher") %>%arrange(name) %>%relocate(publisher, yr_founded, .after = gender)
# A tibble: 7 × 5
name alignment gender publisher yr_founded
<chr> <chr> <chr> <chr> <int>
1 Batman good male DC 1934
2 Catwoman bad female DC 1934
3 Joker bad male DC 1934
4 Magneto bad male Marvel 1939
5 Mystique bad female Marvel 1939
6 Storm good female Marvel 1939
7 <NA> <NA> <NA> Image 1992
Inner Join
In inner join, only rows with common values are returned and rows are multiplied if there are multiple matchings.
inner_join(superheroes, publishers, by ="publisher")
# A tibble: 6 × 5
name alignment gender publisher yr_founded
<chr> <chr> <chr> <chr> <int>
1 Magneto bad male Marvel 1939
2 Storm good female Marvel 1939
3 Mystique bad female Marvel 1939
4 Batman good male DC 1934
5 Joker bad male DC 1934
6 Catwoman bad female DC 1934
Semi Join
Semi Join is very similar to inner join but without columns from Y.
semi_join(superheroes, publishers, by ="publisher")
# A tibble: 6 × 4
name alignment gender publisher
<chr> <chr> <chr> <chr>
1 Magneto bad male Marvel
2 Storm good female Marvel
3 Mystique bad female Marvel
4 Batman good male DC
5 Joker bad male DC
6 Catwoman bad female DC
Full Join
Full join returns all rows and columns from both X and Y and both multiplies multiple matchings and compensates for missing matches.
full_join(superheroes, publishers, by ="publisher")
# A tibble: 8 × 5
name alignment gender publisher yr_founded
<chr> <chr> <chr> <chr> <int>
1 Magneto bad male Marvel 1939
2 Storm good female Marvel 1939
3 Mystique bad female Marvel 1939
4 Batman good male DC 1934
5 Joker bad male DC 1934
6 Catwoman bad female DC 1934
7 Hellboy good male Dark Horse Comics NA
8 <NA> <NA> <NA> Image 1992
Anti Join
Anti join returns all rows from X which do not have information (based on key column) in Y and returns only columns from X.
anti_join(superheroes, publishers, by ="publisher")
# A tibble: 1 × 4
name alignment gender publisher
<chr> <chr> <chr> <chr>
1 Hellboy good male Dark Horse Comics