如何在WHERE子句中避免使用coalesce()/CASE WHEN并处理空值

嘿，这个场景我之前写动态过滤条件时经常碰到，完全可以用基础的逻辑运算符组合来替代coalesce，效果更清晰，还能避免函数调用可能带来的索引失效问题。咱们先拆解下原表达式的逻辑，再给出对应的替代方案：

先搞懂原表达式的真实行为 #

col = coalesce(@var, col)的逻辑其实是：

• 当@var不为NULL时，等价于col = @var，只返回col等于@var的记录
• 当@var为NULL时，条件变成col = col——但要注意SQL的NULL特性：NULL = NULL的结果是UNKNOWN，所以这个条件会自动过滤掉col为NULL的行，只保留col非空的记录

方案1：和原表达式逻辑完全一致的写法 #

如果你的需求就是原表达式的行为（@var为NULL时只保留col非空的行），可以写成：

(@var IS NOT NULL AND col = @var) OR (@var IS NULL AND col IS NOT NULL)

方案2：更常见的需求——@var为NULL时返回所有行 #

很多人用col=coalesce(@var,col)时，其实是想实现“当参数为空时忽略这个过滤条件，返回所有行”，但原表达式做不到这一点（因为会过滤掉col为NULL的行）。这种情况下，正确的写法是：

@var IS NULL OR col = @var

这个写法的逻辑很直观：

• 只要@var是NULL，整个条件直接为TRUE，返回所有行（包括col为NULL的）
• 当@var不为NULL时，就匹配col等于@var的记录

可选：数据库特定的简化写法 #

如果你的数据库支持NULL安全相等运算符（比如MySQL的<=>、PostgreSQL的IS NOT DISTINCT FROM），还可以进一步简化：

• MySQL/ MariaDB：

@var IS NULL OR col <=> @var

• PostgreSQL：

@var IS NULL OR col IS NOT DISTINCT FROM @var

不过这类运算符不是ANSI SQL标准，跨数据库使用时要谨慎。

内容的提问来源于stack exchange，提问作者Zaynul Abadin Tuhin