Oracle数据库含空值参数的精确匹配SQL查询问题

问题场景 #

现有TABLE_NAME表，包含A、B、C三个VARCHAR类型字段，表内有三条数据。传入参数A=A1、B=null、C=null时，需要精确匹配出A=A1且B、C均为null的那条数据，但以下两种SQL均返回全部三条数据：

1. 第一种SQL：

SELECT * FROM TABLE_NAME 
WHERE (@A IS NULL OR A=@A) 
AND (@B IS NULL OR B=@B) 
AND (@C IS NULL OR C=@C)

2. 第二种SQL：

SELECT * FROM TABLE_NAME WHERE A = NVL(:@A , A) AND B = NVL(:@B , B) AND C = NVL(:@C , C)

原因分析 #

• 第一种SQL的逻辑是参数为null时忽略该字段的匹配条件，而非要求字段为null。当B、C参数为null时，@B IS NULL和@C IS NULL为真，对应的条件直接成立，只要A=A1就会被选中，因此返回所有A=A1的数据。
• 第二种SQL使用NVL函数，当参数为null时，条件变为B=B、C=C，但Oracle中NULL = NULL的结果是FALSE，若表中存在非null的B/C数据，B=B会成立，而null的B/C会不成立，核心逻辑与需求不符。

正确SQL语句 #

要实现参数为null时要求对应字段必须为null，参数不为null时精确匹配字段值，可使用以下两种写法：

写法一：显式判断参数与字段的null状态

SELECT * FROM TABLE_NAME
WHERE
  (:A IS NOT NULL AND A = :A) OR (:A IS NULL AND A IS NULL)
  AND (:B IS NOT NULL AND B = :B) OR (:B IS NULL AND B IS NULL)
  AND (:C IS NOT NULL AND C = :C) OR (:C IS NULL AND C IS NULL);

写法二：使用NVL2函数简化逻辑

NVL2函数逻辑为：若第一个参数不为null，返回第二个参数；否则返回第三个参数，用它可简化条件判断：

SELECT * FROM TABLE_NAME
WHERE
  NVL2(:A, A = :A, A IS NULL)
  AND NVL2(:B, B = :B, B IS NULL)
  AND NVL2(:C, C = :C, C IS NULL);

说明 #

两种写法逻辑完全一致：

• 当传入参数（如:B）为null时，判断对应字段（B）是否为null；
• 当传入参数不为null时，判断字段值是否与参数相等。
  以此精确匹配出A=A1且B、C均为null的目标数据。

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