It took me a long time to wrap my head around the different types of joins when I first started learning them, so here's a few examples with some excellent mini datasets from dplyr designed specifically for this purpose!

dplyr::left_join, dplyr::inner_join, dplyr::anti_join, dplyr::semi_join, dplyr::full_join.md

Datasets

Code:

library(dplyr)
band_members

Output:

# A tibble: 3 x 2
  name  band   
  <chr> <chr>  
1 Mick  Stones 
2 John  Beatles
3 Paul  Beatles

Code:

band_instruments

Output:

# A tibble: 3 x 2
 name  plays 
 <chr> <chr> 
1 John  guitar
2 Paul  bass  
3 Keith guitar

Which band members play which instruments?

Code:

left_join(band_members, band_instruments, by = "name")

Output:

# A tibble: 3 x 3
  name  band    plays 
  <chr> <chr>   <chr> 
1 Mick  Stones  NA    
2 John  Beatles guitar
3 Paul  Beatles bass 

Which band members are in both tables, and what do they play?

Code:

inner_join(band_members, band_instruments, by = "name")

Output:

# A tibble: 2 x 3
  name  band    plays 
  <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass  

Which of the band members don't have an instrument listed?

Code:

anti_join(band_members, band_instruments, by = "name")

Output:

# A tibble: 1 x 2
  name  band  
  <chr> <chr> 
1 Mick  Stones

Which of the band members do have an instrument listed (don't tell me about their instrument though!)?

Code:

semi_join(band_members, band_instruments, by = "name")

Output:

  name  band   
  <chr> <chr>  
1 John  Beatles
2 Paul  Beatles

Get me all of the information from both tables, matching where possible!

Code:

full_join(band_members, band_instruments, by = "name")

Output:

# A tibble: 4 x 3
  name  band    plays 
  <chr> <chr>   <chr> 
1 Mick  Stones  NA    
2 John  Beatles guitar
3 Paul  Beatles bass  
4 Keith NA      guitar