如何在dbplyr::left_join.tbl_lazy中保留y表匹配列及SQL转tidyverse实现

原SQL需求 #

你给出的SQL实现的逻辑是：左连接表A和表B（按id字段匹配），最终返回A的id，并根据B中是否存在对应id调整load列——如果B里有匹配的id，load设为NULL；否则保留A原有的load值。对应的SQL代码：

SELECT 
    A.id,
    CASE WHEN B.id IS NOT NULL THEN NULL ELSE A.load END AS load
FROM 
    A
LEFT JOIN 
    B ON A.id = B.id;

转换为tidyverse的几种方法 #

方法1：直接利用左连接后的B.id状态判断 #

左连接后，B中没有匹配的行时，B的所有列都会是NA，所以可以直接通过B.id是否为NA来判断，不需要额外添加辅助列：

library(dplyr)
library(dbplyr)

con <- simulate_mssql()
A <- tbl_lazy(data.frame(id = 1:10, load = "x"), con, name = "A")
B <- tbl_lazy(data.frame(id = 1:5), con, name = "B")

left_join(A, B, by = "id") %>%
  mutate(load = if_else(!is.na(B.id), NA_character_, load)) %>%
  select(id, load) # 只保留需要的两列

生成的SQL会和原逻辑高度一致：

SELECT `id`, IIF(NOT ISNA(`B.id`), NULL, `load`) AS `load`
FROM (
  SELECT `A`.`id`, `A`.`load`, `B`.`id` AS `B.id`
  FROM `A`
  LEFT JOIN `B`
    ON (`A`.`id` = `B`.`id`)
) AS `q01`

方法2：添加辅助列的方式（你提到的思路优化） #

如果不想直接引用B的列，可以给B加个标记列，最后记得移除这个辅助列：

left_join(A, B %>% mutate(flag = "x"), by = "id") %>% 
  mutate(load = if_else(!is.na(flag), NA_character_, load)) %>%
  select(id, load) # 删掉辅助列flag

方法3：拆分合并的思路 #

把A中与B匹配和不匹配的行分开处理，再合并结果：

# 提取A中与B不匹配的行，保留原load值
non_matching <- anti_join(A, B, by = "id")
# 提取A中与B匹配的行，将load设为NA
matching <- semi_join(A, B, by = "id") %>% mutate(load = NA_character_)

# 合并两行结果，按id排序
bind_rows(non_matching, matching) %>% arrange(id)

这种方式生成的SQL会是两个子查询通过UNION ALL合并，逻辑也很直观。

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