Oracle 19c 入参为null时查询所有值失效原因及解决方法

首先明确SQL标准的NULL比较规则：在Oracle中，使用=运算符对NULL进行等值判断时，结果始终为未知（UNKNOWN），WHERE子句仅会保留判断结果为TRUE的行，因此NULL = NULL不会被判定为满足条件。

你使用的WHERE NAME = NVL(?, NAME)写法本身存在逻辑漏洞：当传入参数为NULL时，条件等价于NAME = NAME，此时NAME字段为NULL的行都会因NULL=NULL的判断结果为UNKNOWN被过滤，不符合你需要返回所有记录的需求。

该写法在18c可正常运行是因为Oracle 18c的优化器对这类动态NVL条件的处理存在特殊逻辑，部分场景下会将NAME=NAME这类条件识别为恒真条件，忽略NULL比较规则直接返回所有行；Oracle 19c对优化器的谓词校验逻辑做了修正，严格遵循SQL标准的NULL比较规则，因此原有的逻辑漏洞直接暴露。你后续尝试的NAME=NAME写法也存在同样的NULL比较问题，自然无法得到预期结果。

下面几个方案都可以实现需求，可根据实际场景选择：

• 方案1（通用兼容，无需依赖Oracle特性，适配所有版本）：通过OR逻辑覆盖参数为NULL的场景

SELECT NAME, SURNAME FROM MY_TABLE 
WHERE (? IS NULL OR NAME = ?);

如果需要避免重复传参，可通过CTE或子查询封装参数，或选择下面的Oracle专属方案。

• 方案2（Oracle专属，无需重复传参）：使用DECODE函数，DECODE会默认将两个NULL判定为相等

SELECT NAME, SURNAME FROM MY_TABLE
WHERE DECODE(?, NULL, 1, NAME, ?, 1, 0) = 1;

逻辑说明：当传入参数为NULL时，DECODE直接返回1，所有行都满足条件；参数不为NULL时，仅返回NAME与参数相等的行。

• 方案3（符合SQL标准，适配Oracle 12c及以上版本）：使用IS NOT DISTINCT FROM运算符，该运算符原生支持NULL值的等值判断

SELECT NAME, SURNAME FROM MY_TABLE
WHERE ? IS NULL OR NAME IS NOT DISTINCT FROM ?;

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