postgreSql not in 问题记录

先看结论:  not in ( 结果集 ) 的结果集里面不能有null,否则不会有结果.

可以使用 where xx is not null, left join, not exists 等方法解决

案例: 

CREATE TABLE user_a(
    user_name varchar(20),
);

CREATE TABLE user_b(
    user_name varchar(20),
);

表user_a有记录:

• aaa
• bbb

表user_b有记录:

• bbb
• ccc
• null

SQL1:

select user_name from user_a

where user_name not in (select user_name from user_b);

Result1:

  空

结论1: not in 后面的集合中有null值,会导致查询失效

SQL2:

select user_name from user_a

where user_name not in (select user_name from user_b where user_name is not null);

Result1:

• aaa

结论2: 使用 not in 后的集合中 使用 is not null 避免查询失效

其他方案: 使用 not exists或者left join都可以.

not exists:

select user_name from user_a a 
where not exists (
  select user_name from user_b b where a.user_name = b.user_name
)

left join:

select user_name from user_a a left join user_b b

on a.user_name = b.user_name

where a.user_name is null