如何在dbplyr的inner_join SQL转换中使用LIKE实现前缀匹配？

我在SQL服务器端有两张表，定义代码如下：

data <- memdb_frame(
  x = c("ANOTHER", "COMMON", "ZEBRA")
)

selection <- memdb_frame(
  x_prefix = c("ANO", "B", "CO"),
  type = c("One type", "Other type", "Other type")
)

需求是将这两张表进行内连接，只保留x_prefix作为x前缀的行。

我最初尝试的dbplyr代码是：

inner_join(data, selection, by = c(x = "x_prefix")) |>
  show_query()

这段代码生成的SQL是：

<SQL>
SELECT `x`, `type`
FROM `dbplyr_001` AS `LHS`
INNER JOIN `dbplyr_002` AS `RHS`
ON (`LHS`.`x` = `RHS`.`x_prefix`)

但这不符合需求，我需要的目标SQL是：

<SQL>
SELECT `x`, `type`
FROM `dbplyr_001` AS `LHS`
INNER JOIN `dbplyr_002` AS `RHS`
ON (`LHS`.`x` LIKE `RHS`.`x_prefix` || '%')

我已经查阅过dbplyr的函数转换相关文档，但仍需要帮助实现正确的连接逻辑。

实现方法 #

有两种方式可以生成符合需求的SQL：

• 方法一：利用join_by和str_starts函数
  dbplyr会自动将str_starts转换为SQL的LIKE语法，代码如下：

  inner_join(
    data,
    selection,
    join_by(str_starts(x, x_prefix))
  ) |> show_query()
  

• 方法二：直接指定SQL连接条件
  通过sql_on参数直接写入自定义的SQL连接逻辑：

  inner_join(
    data,
    selection,
    sql_on = "LHS.x LIKE RHS.x_prefix || '%'"
  ) |> show_query()
  

这两种方法都会生成你需要的目标SQL，实现基于前缀匹配的内连接。

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