SQL筛选指定列同时存在NULL与非NULL值行的简洁写法

问题说明 #

现有测试表数据如下：

id     a      b      c   time
 ----------------------------- 
 0      1      4   "ca"     23 
 1   NULL   NULL   NULL     18
 2   NULL      1   "pn"     13
 3      6   NULL   "ar"     27
 4      1      2   NULL     24

需求是筛选出指定列子集（示例中为a、b、c三列）里同时存在至少1个NULL值、至少1个非NULL值的行，期望返回结果：

id     a      b      c   time
 ----------------------------- 
 2   NULL      1   "pn"     13
 3      6   NULL   "ar"     27
 4      1      2   NULL     24

原有多层嵌套OR/AND的写法在列数增多时会非常冗余，以下是通用的简洁实现方案。

核心思路 #

不需要写多层嵌套的逻辑判断，这类需求本质只需要排除两类不符合要求的行即可：

1. 待校验列子集中所有值全为NULL
2. 待校验列子集中所有值全为非NULL
   剩下的所有行都满足混合存在NULL和非NULL的要求，逻辑非常清晰，不管校验多少列都不会混乱。

具体实现方案 #

1. 标准SQL写法（全数据库兼容） #

用COALESCE函数判断全NULL：该函数会返回参数列表里第一个非NULL值，如果所有待校验列全为NULL，函数返回NULL；再搭配全非NULL的判断即可，示例代码：

SELECT *
FROM your_table
WHERE 
  -- 排除全NULL的行
  NOT COALESCE(a,b,c) IS NULL
  -- 排除全非NULL的行
  AND NOT (a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL)

如果需要新增校验列，只需要在COALESCE的参数列表和全非NULL判断里同步添加列名即可，逻辑直白不容易出错。

2. 支持行表达式比较的数据库（最简写法） #

PostgreSQL、MySQL 8.0.28+、SQLite、SQL Server 2022+等数据库支持行级别的NULL判断，可以直接把待校验列写成行构造器，不需要逐列写判断，哪怕校验20列以上也只需要把列名放进括号，代码极简洁：

SELECT *
FROM your_table
WHERE 
  NOT (a,b,c) IS NULL -- 排除全NULL行
  AND NOT (a,b,c) IS NOT NULL -- 排除全非NULL行

3. 老版本数据库兼容写法（统计NULL个数） #

如果是不支持上述特性的老版本数据库，可以通过统计待校验列中NULL值的个数实现：只要NULL的个数大于0（存在NULL）且小于待校验列总数（不是全NULL），就符合要求。以3列为例：

SELECT *
FROM your_table
WHERE 
  (
    CASE WHEN a IS NULL THEN 1 ELSE 0 END
    + CASE WHEN b IS NULL THEN 1 ELSE 0 END
    + CASE WHEN c IS NULL THEN 1 ELSE 0 END
  ) BETWEEN 1 AND 2 -- 3列时，NULL个数在1-2之间即为混合状态

新增校验列时只需要多叠加一段CASE WHEN统计，同时调整BETWEEN的上限为「列总数-1」即可。

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