根据输入参数在WHERE子句匹配多值（含空值）的SQL实现问题

问题原因 #

你编写的SQL存在两处逻辑错误：

• CASE表达式的判断对象错误，你是基于表字段TYPE做判断，而非基于输入参数:IP_TYPE的值来生成匹配规则
• 空值、空字符串匹配条件被全局生效，无论入参是HIGH还是LOW都会命中空值场景，不符合LOW仅匹配TYPE='LOW'的规则

修复方案 #

方案1：逻辑分支写法（全数据库兼容，可读性最高）

SELECT * FROM PAYRECORDS
WHERE 
  (:IP_TYPE = 'LOW' AND TYPE = 'LOW')
  OR
  (:IP_TYPE = 'HIGH' AND (TYPE = 'HIGH' OR TYPE = '' OR TYPE IS NULL));

方案2：CASE表达式写法

SELECT * FROM PAYRECORDS
WHERE CASE 
  WHEN :IP_TYPE = 'LOW' THEN TYPE = 'LOW'
  WHEN :IP_TYPE = 'HIGH' THEN (TYPE = 'HIGH' OR TYPE = '' OR TYPE IS NULL)
END;

注：部分数据库（如老版本Oracle）不支持CASE直接返回布尔值，优先使用方案1即可。

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