join_by() allowing OR

[oloverm]

The join_by(x == y) syntax makes it feel like we should be able to specify multiple join conditions with OR, like you can in SQL. Along the lines of join_by(x == y | x == z). I've come across situations where that would be useful, and manually working around it gives pretty long and much less expressive code. See below, where I want to join x and y on columns a or b.

library(dplyr, warn.conflicts = FALSE)

x <- tibble::tribble(
  ~a, ~b,
   1, 10,
   2, 20,
   3, 30
  )

y <- tibble::tribble(
  ~a, ~b,      ~fruit,
   1, NA, "apple",
  NA, 20,  "pear"
  )

# My best shot at joining on a | b currently
x |> 
  left_join(y |> 
              select(a, fruit),
            join_by(a == a)) |> 
  left_join(y |> 
              select(b, fruit),
            join_by(b == b)) |> 
  mutate(fruit = coalesce(fruit.x, fruit.y)) |> 
  select(-c(fruit.x, fruit.y))
#> # A tibble: 3 × 3
#>       a     b fruit
#>   <dbl> <dbl> <chr>
#> 1     1    10 apple
#> 2     2    20 pear 
#> 3     3    30 <NA>


# What I wish I could run:
# left_join(x, y, by = join_by(a == a | b == b))

^{Created on 2024-03-28 with reprex v2.1.0}

[DavisVaughan]

The dplyr join API has always been about "and" conditions. Unfortunately it would be pretty difficult to also add a way to perform "or" conditions and the demand doesn't seem too high for this, so as of today this isn't planned