在做一些类似黑名单的功能时，会使用 not in 来过滤。比如：

select * from users where id not in (1,2,3);

// 或者

select * from users 
where id not in (
  select user_id from blacklist
)

但是我们经常忽略一个前提条件，这两个查询能够满足需求的前提是：in后面的返回值列表不包含NULL；

那么现在来看一下各种情况not in的返回值：

select 
  1 not in (2,3) as "1 not in (2,3)", 
  1 not in (1,3) as "1 not in (1,3)",
  1 not in (1,null) as "1 not in (1, null)", 
  1 not in (2, null) as "1 not in (2, null)";

 1 not in (2,3) | 1 not in (1,3) | 1 not in (1, null) | 1 not in (2, null)
----------------+----------------+--------------------+--------------------
 t              | f              | f                  | (null)

前三个都符合预期，下面来看第四种情况：

select 1 not in (2, null);
 ?column?
----------
 (null)
(1 row)

查了一下资料，返回NULL的原因是：

1 not in (2, null)被翻译成了 1 != 2 and 1 != null => true and null => null

所以，在 not in 相关查询里一定要注意 in 后面是不是有可能包含 NULL 值，否则结果可能不符合预期。

select * from users 
where id not in (
  select user_id from blacklist where user_id is not null
)

ref：https://ossdaily.com/hooopo/not-in-cai-keng-ji-lu-hpa