R查询SQL数据库时过滤条件放置位置不同返回结果不一致问题

问题背景 #

从数据库表提取子集时，需要同时应用三个逻辑与（AND）关系的过滤规则，理论上无论过滤顺序、执行端（SQL端/R端）如何选择，最终返回结果应当完全一致，但实际测试中三种写法返回结果差异极大。
三个过滤规则如下：

• YEAR 字段值属于给定集合years
• SYSTEM 字段值不等于'G'
• CODE 字段值等于给定变量code

不同实现的返回结果 #

实现1：三个过滤条件全写入SQL执行 #

返回结果：空表，无任何数据
对应代码：

table1 <- dbGetQuery(
  conn = connection, 
  statement = paste0("SELECT * FROM database WHERE YEAR IN (",
                     paste0(years, collapse = ","), ") AND SYSTEM != 'G'" AND CODE = ", code))

实现2：SQL端应用YEAR、SYSTEM过滤，R端过滤CODE #

返回结果：914行数据
对应代码：

table2 <- dbGetQuery(
  conn = connection, 
  statement = paste0("SELECT * FROM database WHERE YEAR IN (",
                     paste0(years, collapse = ","), ") AND SYSTEM != 'G'")) %>%
  filter(CODE == code)

实现3：SQL端应用SYSTEM、CODE过滤，R端过滤YEAR #

返回结果：184行数据
对应代码：

table3 <- dbGetQuery(
  conn = connection, 
  statement = paste0("SELECT * FROM database WHERE SYSTEM != 'G' AND CODE = ", code)) %>%
  filter(YEAR %in% years)

问题根源 #

1. 实现1的SQL拼接存在低级语法错误：paste0拼接语句中，") AND SYSTEM != 'G'" AND CODE = " 段提前闭合了双引号，后续AND CODE = 的内容没有被拼接到SQL字符串内，反而被R当成独立代码执行，最终生成的SQL语句完全不符合预期，返回空表是必然结果。
2. 核心差异来自SQL拼接时未做类型适配，导致SQL端匹配逻辑和R端不等价：
   • 若数据库中CODE是字符型字段，拼接CODE = 后的code变量时没有用单引号包裹，数据库会对传入值做隐式类型转换：比如字符串格式的编码"00123"会被转为数值123，仅能匹配数值型的123，无法匹配带前导零的字符型编码；而R端filter(CODE == code)默认做精确的类型匹配，两者匹配范围完全不同。
   • 若years集合中存在非数值的年份值（如带前缀的财年标识、字符格式年份），直接拼接不加单引号会导致SQL端IN逻辑匹配错误，和R端%in%的精确匹配结果产生偏差。
3. 存在小概率的NULL值逻辑差异：SQL中SYSTEM != 'G'会直接排除SYSTEM字段为NULL的行，若R读取数据时NULL被转为NA，部分dplyr版本对NA值的过滤规则和SQL不一致，但该场景下数百行的差异量级显然不是该原因导致。

修复方案 #

优先使用参数化查询替代手动拼接SQL，从根源避免引号闭合错误、类型转义错误，同时避免SQL注入风险，参考写法：

# 注意不同数据库驱动的参数占位符可能有差异，部分驱动用$1、$2做占位
query_sql <- "SELECT * FROM database WHERE YEAR IN (?) AND SYSTEM != 'G' AND CODE = ?"
table_correct <- dbGetQuery(
  conn = connection,
  statement = query_sql,
  params = list(years, code)
)

若暂时不使用参数化查询，手动拼接时必须注意：字符型的值需要用单引号包裹，拼接完成后建议先打印生成的SQL语句检查语法和值格式是否正确，参考拼接逻辑：

# 若years是字符型向量，需要给每个值包裹单引号
years_str <- paste0("'", years, "'", collapse = ",")
correct_sql <- paste0(
  "SELECT * FROM database WHERE YEAR IN (", years_str,
  ") AND SYSTEM != 'G' AND CODE = '", code, "'"
)
# 打印检查拼接后的SQL是否符合预期
print(correct_sql)

修复后无论过滤条件放置在SQL端还是R端，返回结果都会完全一致。

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