SQL中CASE表达式无法匹配NULL的问题及日期参数逻辑实现需求

需求说明 #

需要编写SQL查询实现以下逻辑：

• 当日期参数P_D_IN_END_DT传入'01/01/1900'、'01/01/1'或NULL时，返回TRUNC(SYSDATE)
• 其他情况直接返回参数值

原代码问题 #

原实现代码如下：

SELECT
     CASE
          WHEN  P_D_IN_END_DT IN ( TO_DATE ('01/01/1900', 'dd/mm/yyyy'),  TO_DATE ('01/01/1', 'dd/mm/yyyy'),  NULL)
          THEN
             TRUNC(SYSDATE)
          ELSE
             P_D_IN_END_DT
       END 
       AS END_DT
FROM DUAL;

测试发现，当参数为'01/01/1900'或'01/01/1'时逻辑正常，但参数为NULL时，未返回预期的TRUNC(SYSDATE)，而是返回NULL。测试代码：

SELECT CASE
          WHEN NULL IN
                  (TO_DATE ('01/01/1900', 'dd/mm/yyyy'),
                   TO_DATE ('01/01/1', 'dd/mm/yyyy'), NULL)         
          THEN
             TRUNC(SYSDATE)
          ELSE
             NULL
       END
    AS END_DT
FROM DUAL;

问题原因 #

SQL中任何与NULL的比较结果都是UNKNOWN，而CASE语句只会在条件判断结果为TRUE时执行对应的分支。使用IN操作符时，NULL IN (...)的结果是UNKNOWN，不会触发THEN分支，因此参数为NULL时会走到ELSE返回NULL。

修复后的代码 #

把NULL的情况单独用IS NULL判断，通过OR和其他条件组合：

SELECT
     CASE
          WHEN P_D_IN_END_DT IS NULL 
               OR P_D_IN_END_DT IN (TO_DATE('01/01/1900', 'dd/mm/yyyy'), TO_DATE('01/01/1', 'dd/mm/yyyy'))
          THEN
             TRUNC(SYSDATE)
          ELSE
             P_D_IN_END_DT
       END 
       AS END_DT
FROM DUAL;

验证测试 #

测试参数为NULL的场景：

SELECT CASE
          WHEN NULL IS NULL 
               OR NULL IN (TO_DATE('01/01/1900', 'dd/mm/yyyy'), TO_DATE('01/01/1', 'dd/mm/yyyy'))
          THEN
             TRUNC(SYSDATE)
          ELSE
             NULL
       END
    AS END_DT
FROM DUAL;

此时会返回当前日期的截断值，符合预期。其他参数场景也能正常工作。

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