SQL最佳实践：WHERE子句中NOT IN与<>的性能差异及适用场景

性能差异结论 #

绝大多数主流关系型数据库（MySQL、PostgreSQL、Oracle、SQL Server等）的查询优化器，都会将静态常量值数量较少的NOT IN ('a', 'b', 'c', 'd')和col <> 'a' AND col <> 'b' AND col <> 'c' AND col <> 'd'识别为等价逻辑，最终生成的执行计划完全一致，没有可观测的性能差异。
仅在两类场景下会出现性能差异：

• 当NOT IN后接的是子查询结果而非静态常量时，优化器通常不会做等价转换。如果子查询返回的结果集较大，NOT IN走哈希匹配的执行效率会远高于逐个判断的多<> 条件拼接。
• 当需要排除的常量值数量超过10个时，部分数据库的优化器解析多<> 拼接条件的成本会明显升高，NOT IN的执行计划生成速度和运行速度都会更优。

优先使用NOT IN的场景 #

除了代码更简洁外，以下场景优先选NOT IN：

• 排除值为动态生成的集合：比如业务代码中批量拼接要排除的ID列表，直接生成NOT IN (?)格式的预编译语句参数列表，比循环拼接AND col <> ?的开发成本低很多，也能避免漏写AND关键字的语法错误。
• 排除值数量大于3个：多<> 拼接的条件超过3个后可读性会快速下降，后期新增/删除排除值时很容易出现漏改、错改的问题。
• 排除值来自其他表的查询结果：直接写NOT IN (SELECT val FROM other_table WHERE 过滤条件)即可实现逻辑，不需要先把取值查出来再拼接SQL，灵活性更高。
• 需要跨数据库兼容：NOT IN是SQL标准语法，所有关系型数据库都支持，部分小众数据库对多条件拼接的解析优化不如标准语法稳定。

选择建议 #

• 静态排除值≤3个：二者无性能差异，按照团队代码规范选择即可。
• 静态排除值>3个：优先选NOT IN，可读性和性能都更优。
• 动态生成排除集合：必须选NOT IN，预编译语句的性能更高，也能规避SQL注入风险。

注意：如果NOT IN的列表中可能出现NULL值，不要使用NOT IN（NOT IN遇到NULL时会返回空集，不符合预期逻辑），这种场景建议提前过滤掉NULL值或者用NOT EXISTS替代，不要使用多<> 拼接。

内容的提问来源于stack exchange，提问作者Chicken Sandwich No Pickles