Oracle SQL中NVL在WHERE子句未返回含NULL行的解决方案咨询

当MY_VARIABLE为NULL时，nvl(:MY_VARIABLE, fruit_name)会返回fruit_name本身，此时WHERE子句条件变为fruit_name = fruit_name。但SQL中NULL与任何值（包括自身）的比较结果都是UNKNOWN，WHERE子句仅保留条件结果为TRUE的行，因此fruit_name为NULL的行被过滤。

以下几种方式均可实现需求：当MY_VARIABLE为NULL时返回所有行，非NULL时匹配对应fruit_name的记录：

方法1：拆分条件（通用且直观） #

直接判断变量状态，变量为NULL时返回全表，否则匹配具体值：

SELECT * 
FROM fruits
WHERE :MY_VARIABLE IS NULL OR fruit_name = :MY_VARIABLE;

该写法逻辑清晰，兼容所有SQL方言，执行效率优异。

方法2：通过NVL统一NULL的匹配规则 #

为NULL映射一个不会出现在实际数据中的特殊值，让NULL之间的比较等价于相等：

SELECT * 
FROM fruits
WHERE NVL(fruit_name, '###SPECIAL_NULL###') = NVL(:MY_VARIABLE, '###SPECIAL_NULL###');

注意：需确保'###SPECIAL_NULL###'不会在fruit_name字段中出现，避免错误匹配。若不确定，可使用CHR(0)这类极难出现的特殊字符。

方法3：Oracle专属操作符（12c及以上版本） #

Oracle 12c+支持IS NOT DISTINCT FROM，该操作符将NULL视为与自身相等，结合条件判断实现需求：

SELECT * 
FROM fruits
WHERE :MY_VARIABLE IS NULL OR fruit_name IS NOT DISTINCT FROM :MY_VARIABLE;

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