9.4 many to many

In a many to many join, both tables will have duplicate keys in them. You end up with what’s known as a cartesian product of the keys.

(x <- tribble(
    ~key, ~val_x,
    1, "x1",
    2, "x2",
    3, "x3",
    1, "x4",
    1, "x5",
    1, "x6"
))
## # A tibble: 6 x 2
##     key val_x
##   <dbl> <chr>
## 1     1 x1   
## 2     2 x2   
## 3     3 x3   
## 4     1 x4   
## 5     1 x5   
## 6     1 x6

(y <- tribble(
    ~key, ~val_y,
    1, "y1",
    2, "y2",
    4, "y3",
    1, "y4",
    1, "y5"
))
## # A tibble: 5 x 2
##     key val_y
##   <dbl> <chr>
## 1     1 y1   
## 2     2 y2   
## 3     4 y3   
## 4     1 y4   
## 5     1 y5

A many to many join creates a cartesian product

x %>%
    left_join(y, by = 'key')
## # A tibble: 14 x 3
##      key val_x val_y
##    <dbl> <chr> <chr>
##  1     1 x1    y1   
##  2     1 x1    y4   
##  3     1 x1    y5   
##  4     2 x2    y2   
##  5     3 x3    <NA> 
##  6     1 x4    y1   
##  7     1 x4    y4   
##  8     1 x4    y5   
##  9     1 x5    y1   
## 10     1 x5    y4   
## 11     1 x5    y5   
## 12     1 x6    y1   
## 13     1 x6    y4   
## 14     1 x6    y5