Redshift中CASE WHEN与NVL的SQL语句是否完全等价？

在Redshift环境中，仅当P.PRED_VAL的值为**NaN（非数值）**时，你同事写的CASE语句和NVL(P.PRED_VAL, R.SCORE_VAL)的结果会出现差异。

差异原因 #

Redshift对NaN的判断逻辑有特殊处理：

• NVL函数会将NaN视为非NULL值，直接返回第一个参数（即P.PRED_VAL本身）；
• 而CASE语句中的WHEN P.PRED_VAL IS NULL判断，在Redshift中NaN IS NULL会返回true，此时CASE会执行THEN分支，返回R.SCORE_VAL。

测试验证 #

可以通过以下测试SQL直观看到差异：

-- 创建临时测试表
CREATE TEMP TABLE RESULT (ID INT, SCORE_VAL NUMERIC);
CREATE TEMP TABLE PREDICTION (ID INT, PRED_VAL NUMERIC);

-- 插入测试数据：包含NaN和NULL的情况
INSERT INTO RESULT VALUES (1, 85), (2, 90);
INSERT INTO PREDICTION VALUES (1, NaN), (2, NULL);

-- 对比两种写法的输出
SELECT
    ID,
    CASE
        WHEN P.PRED_VAL IS NULL THEN R.SCORE_VAL
        ELSE P.PRED_VAL
    END AS CASE_FINAL_SCORE,
    NVL(P.PRED_VAL, R.SCORE_VAL) AS NVL_FINAL_SCORE
FROM RESULT R
INNER JOIN PREDICTION P
    ON R.ID = P.ID;

执行结果：

可以看到，当P.PRED_VAL为NaN时，两种写法的结果完全不同；而当P.PRED_VAL为普通NULL时，结果一致。

补充说明 #

Redshift基于PostgreSQL开发，但在NaN的处理上与原生PostgreSQL有区别：原生PostgreSQL中NaN IS NULL返回false，但Redshift调整了这个逻辑，这也是导致差异的核心原因。

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