多同类型CNPJ字段IN筛选：替代UNION ALL的方案及最优性问询

当然有不需要UNION ALL的实现方式，同时我们也可以聊聊当前方案的优劣：

方案一：单查询结合OR条件 #

你可以把四个字段的匹配逻辑合并到同一个WHERE子句中，用OR连接，这样就避免了多次SELECT和UNION ALL的操作，代码更简洁。示例如下：

SELECT id 
FROM db_armazenamento..arquivo_conhecimento WITH(INDEX(IX_arquivo_conhecimento_6), nolock) 
WHERE dta_inclusao BETWEEN GETDATE() - 20 AND GETDATE()
AND (
    cpf_cnpj_emitente COLLATE database_default IN (SELECT cnpj FROM db_armazenamento..INTEGRA_ORCL_CTE)
    OR cpf_cnpj_destinatario COLLATE database_default IN (SELECT cnpj FROM db_armazenamento..INTEGRA_ORCL_CTE)
    OR cpf_cnpj_remetente COLLATE database_default IN (SELECT cnpj FROM db_armazenamento..INTEGRA_ORCL_CTE)
    OR cpf_cnpj_recebedor COLLATE database_default IN (SELECT cnpj FROM db_armazenamento..INTEGRA_ORCL_CTE)
)

方案二：使用EXISTS子查询 #

另一种更直观的方式是用EXISTS判断当前记录的任意一个CNPJ字段是否存在于目标表中，逻辑上和上面一致，但有时查询优化器会给出更高效的执行计划：

SELECT id 
FROM db_armazenamento..arquivo_conhecimento ac WITH(INDEX(IX_arquivo_conhecimento_6), nolock) 
WHERE dta_inclusao BETWEEN GETDATE() - 20 AND GETDATE()
AND EXISTS (
    SELECT 1 
    FROM db_armazenamento..INTEGRA_ORCL_CTE ic
    WHERE ic.cnpj IN (
        ac.cpf_cnpj_emitente COLLATE database_default,
        ac.cpf_cnpj_destinatario COLLATE database_default,
        ac.cpf_cnpj_remetente COLLATE database_default,
        ac.cpf_cnpj_recebedor COLLATE database_default
    )
)

当前UNION ALL方案的优劣势分析 #

你的原方案并非绝对的“最优解”，需要结合数据分布和执行计划判断：

• 优势：每个子查询都明确指定了索引，查询优化器会对每个分支单独处理，适合四个字段选择性差异较大的场景（比如某一个字段匹配的数据极少，单独扫描效率更高）；另外UNION ALL不会自动去重，如果你需要保留重复的id（比如同一个记录有多个字段匹配CNPJ），原方案会直接返回这些重复项。
• 劣势：需要四次扫描目标表（即使是索引扫描），当表数据量很大时，多次扫描的开销可能会比单次扫描更高；代码冗余度高，后续维护（比如修改时间范围、调整COLLATE规则）需要重复操作四次，容易出错。

如何选择最优方案 #

建议你对比三种方案的执行计划来做决策：

1. 查看原UNION ALL方案的执行计划，确认四个分支是否都高效利用了指定的索引；
2. 对比单查询OR和EXISTS方案的执行计划，重点看扫描行数、逻辑读、CPU占用等指标；
3. 如果业务不需要保留重复的id，可以在所有方案末尾加上DISTINCT，再对比去重后的性能差异。

内容的提问来源于stack exchange，提问作者Felipe Sales Mendes