flexible-joins

flexible-joins.R

library(dplyr)

x <- tibble(
  a1 = c(1, 5, 7, 12),
  a2 = c(4, 6, 11, 15)
)

y <- tibble(
  b = c(2, 3, 1, 4, 6, 0, 14)
)

# Notice `y$b` doesn't have any values in [7, 11]
left_join(x, y, by = join_by(a1 <= b, a2 >= b))
#> # A tibble: 7 x 3
#>      a1    a2     b
#>   <dbl> <dbl> <dbl>
#> 1     1     4     2
#> 2     1     4     3
#> 3     1     4     1
#> 4     1     4     4
#> 5     5     6     6
#> 6     7    11    NA
#> 7    12    15    14

# To also see unmatched values in `y$b`
full_join(x, y, by = join_by(a1 <= b, a2 >= b))
#> # A tibble: 8 x 3
#>      a1    a2     b
#>   <dbl> <dbl> <dbl>
#> 1     1     4     2
#> 2     1     4     3
#> 3     1     4     1
#> 4     1     4     4
#> 5     5     6     6
#> 6     7    11    NA
#> 7    12    15    14
#> 8    NA    NA     0