PostgreSQL SELECT WHERE查询如何过滤指定值同时保留null记录

原因说明 #

SQL采用三值逻辑（TRUE/FALSE/UNKNOWN），NULL和任意值执行=、!=等比较运算时，返回结果都是UNKNOWN。WHERE子句仅会保留判断结果为TRUE的记录，因此你添加customer_id != 21的条件时，customer_id为NULL的行判断结果为UNKNOWN，会被一并过滤。

解决方案 #

写法1：全数据库兼容的显式判断 #

直接在条件中显式允许customer_id为NULL的情况，这是兼容性最好、可读性最高的写法：

SELECT gateway, customer_id
FROM gateways
WHERE gateway = '1000056'
  AND (customer_id != 21 OR customer_id IS NULL);

写法2：标准语法简化写法 #

如果你使用的数据库支持SQL标准的IS NOT DISTINCT FROM运算符（PostgreSQL、SQL Server 2022+、MySQL 8.0.13+等均支持），可以用更简洁的写法，该运算符会将NULL视为普通值参与比较：

SELECT gateway, customer_id
FROM gateways
WHERE gateway = '1000056'
  AND NOT (customer_id IS NOT DISTINCT FROM 21);

不推荐写法：使用COALESCE函数 #

你也可以用COALESCE函数将NULL替换为一个不会和业务值冲突的默认值再做比较：

SELECT gateway, customer_id
FROM gateways
WHERE gateway = '1000056'
  AND COALESCE(customer_id, 0) != 21;

注意该写法需要保证你选择的默认值（示例中为0）绝对不会出现在customer_id的合法业务值中，否则会出现误伤，因此不推荐在生产环境使用。

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