PostgreSQL跨列比较时排除NULL值判断行内非空值是否全匹配

假设有如下数据表：

id   col1    col2    col3    col4
1    A       A       C
2            B       B       B
3    D               D

需求为新增一列，标识当前行中所有非空值是否完全匹配，期望输出结果如下：

id   col1    col2    col3    col4   is_a_match
1    A       A       C              FALSE
2            B       B       B      TRUE
3    D               D              TRUE

已尝试使用如下SQL实现：

select *,
     case  
         when col1 = col2
                and col2 = col3
                and col3 = col4
         then 'TRUE'
         else 'FALSE'
     end as is_a_match
from my_table

但受SQL三值逻辑影响，NULL与任何值做=等值比较的结果都为UNKNOWN，会被CASE WHEN判定为不满足条件，导致所有含NULL的行都返回FALSE，不符合预期。

通用跨引擎方案（兼容MySQL、PostgreSQL、SQL Server、BigQuery等绝大多数主流数据库） #

核心逻辑是利用COALESCE返回参数列表首个非空值的特性，通过错位排列COALESCE的入参，判断所有非空值是否一致：

SELECT *,
  CASE
    WHEN COALESCE(col1, col2, col3, col4) = COALESCE(col2, col3, col4, col1)
     AND COALESCE(col2, col3, col4, col1) = COALESCE(col3, col4, col1, col2)
     AND COALESCE(col3, col4, col1, col2) = COALESCE(col4, col1, col2, col3)
    THEN 'TRUE'
    ELSE 'FALSE'
  END AS is_a_match
FROM my_table;

逻辑说明：

• 只要行内所有非空值完全相等，无论怎么调整COALESCE的入参顺序，返回的第一个非空值都相同，等值判断成立；
• 只要存在任意两个非空值不相等，错位比较时一定会出现结果不一致的情况，判断不成立；
• 整行全为NULL时，所有COALESCE返回值均为NULL，等值判断结果为UNKNOWN，最终返回FALSE，如果业务需要全NULL场景也返回TRUE，只需在WHEN条件最后追加OR (col1 IS NULL AND col2 IS NULL AND col3 IS NULL AND col4 IS NULL)即可。

特定数据库简化写法 #

如果使用支持空值安全比较、且聚合函数会自动跳过NULL的数据库，可以用更简洁的写法：

• PostgreSQL：利用GREATEST/LEAST自动跳过NULL的特性，只要所有非空值相等，行内最大值和最小值必然相等：

SELECT *,
  CASE WHEN GREATEST(col1, col2, col3, col4) = LEAST(col1, col2, col3, col4) THEN 'TRUE' ELSE 'FALSE' END AS is_a_match
FROM my_table;

• MySQL：可以用空值安全等于操作符<=>逐字段比较，不过写法冗余度高于COALESCE通用方案，不做优先推荐。

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