使用IS NULL或Coalesce向WHERE子句传入参数的方法对比

嘿，这个场景我天天碰到，好多开发者在处理可选过滤参数时都会纠结这两种写法！咱们先把事儿说透：

首先我先默认你说的两种写法是最常见的这两类（如果和你实际写的不一样，你可以补充，但大概率是这俩）：

版本1（你说的最快的那种） #

SELECT * 
FROM YourTable
WHERE @CompanyID IS NULL OR CompanyID = @CompanyID

版本2 #

SELECT * 
FROM YourTable
WHERE CompanyID = COALESCE(@CompanyID, CompanyID)

核心区别：对NULL值的处理逻辑 #

这就是你在不同表得到不同结果集的根源！

• 如果你的表中CompanyID字段不允许NULL（NOT NULL约束），那两种写法结果完全一致，性能差异可能也不明显；
• 但如果某张表的CompanyID允许NULL，问题就来了：
  • 版本1：当@CompanyID为NULL时，@CompanyID IS NULL这个条件直接成立，所以所有记录（包括CompanyID为NULL的那些）都会被返回，完全符合你的需求；
  • 版本2：当@CompanyID为NULL时，语句会变成CompanyID = CompanyID，但SQL里NULL = NULL的结果是UNKNOWN，会被WHERE子句过滤掉，所以那些CompanyID为NULL的记录会被漏掉，结果集自然就不一样了！

性能差异为啥这么大？ #

版本1快的原因很简单：当@CompanyID不为NULL时，数据库可以直接用上CompanyID字段上的索引，快速定位匹配的记录；只有当@CompanyID为NULL时，才会走全表扫描（这也是合理的，因为要返回所有数据）。

而版本2里的COALESCE(@CompanyID, CompanyID)是把函数作用在了CompanyID列上，数据库没法利用这个列的索引，不管你传不传NULL，都会强制走全表扫描，速度自然慢很多。

哪个更安全？ #

这里的安全指的是逻辑正确性：

• 版本1完全符合你的需求：参数为NULL返回全部记录，参数非NULL返回匹配结果；
• 版本2在存在NULL值的表中会丢失数据，逻辑上有漏洞，显然不安全。

不过要注意，版本1可能会碰到「参数嗅探」的问题（比如SQL Server中，第一次执行时传了NULL，生成全表扫描的执行计划，之后传非NULL参数时还是用这个计划），如果碰到这种情况，可以在语句末尾加OPTION(RECOMPILE)来强制重新生成执行计划，解决性能问题。

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