如何在dplyr中基于多列的OR条件实现表连接？

在SQL中可以通过OR指定多列作为连接条件实现表连接，现在需要在dplyr中实现类似逻辑：通过a列或b列从查找表y中引入new_info列到主表x中。

示例数据如下：

x <- tibble::tribble(
  ~a,   ~b,  ~old_info,
   1,   10,     "old1",
   2,   20,     "old2",
   3,   30,     "old3",
   4,   40,     "old4",
   5,   50,     "old5"
)

y <- tibble::tribble(
  ~a,   ~b,  ~new_info,
   1,   NA,     "new1",
   2,   NA,     "new2",
  NA,   NA,     "new3",
  NA,   40,     "new4",
  NA,   50,     "new5"
)

期望输出：

#> # A tibble: 5 × 4
#>       a     b old_info new_info
#>   <dbl> <dbl> <chr>    <chr>   
#> 1     1    10 old1     new1    
#> 2     2    20 old2     new2    
#> 3     3    30 old3     NA      
#> 4     4    40 old4     new4    
#> 5     5    50 old5     new5    

dplyr原生的join函数不支持直接使用OR连接条件，我们可以拆分两次连接再合并结果来实现需求：

1. 先清理查找表y，去掉a和b都为NA的行（这类行无法匹配主表任何记录）
2. 分别按a和b列将主表x与清理后的y进行左连接
3. 使用coalesce()函数合并两次连接得到的new_info值，优先保留a列匹配到的结果，无匹配则取b列的结果，均无匹配则为NA

具体代码实现：

library(dplyr)
library(tibble)

x <- tibble::tribble(
  ~a,   ~b,  ~old_info,
   1,   10,     "old1",
   2,   20,     "old2",
   3,   30,     "old3",
   4,   40,     "old4",
   5,   50,     "old5"
)

y <- tibble::tribble(
  ~a,   ~b,  ~new_info,
   1,   NA,     "new1",
   2,   NA,     "new2",
  NA,   NA,     "new3",
  NA,   40,     "new4",
  NA,   50,     "new5"
)

# 清理y表，过滤掉a和b都为NA的无效行
y_clean <- y %>% filter(!is.na(a) | !is.na(b))

# 分两次左连接，合并new_info结果
final_result <- x %>%
  # 按a列连接，获取new_info
  left_join(y_clean %>% select(a, new_info), by = "a") %>%
  # 按b列连接，将结果命名为new_info_b避免列名冲突
  left_join(y_clean %>% select(b, new_info_b = new_info), by = "b") %>%
  # 合并两个new_info列，取第一个非NA值
  mutate(new_info = coalesce(new_info, new_info_b)) %>%
  # 移除临时列
  select(-new_info_b)

# 查看结果
final_result

运行后即可得到期望的输出结果。

内容的提问来源于stack exchange，提问作者Oliver