SQL中IN与NOT IN查询结果不一致问题排查

这是SQL里一个经典的NOT IN陷阱——你的第一条查询的子查询结果中存在NULL值，导致整个NOT IN逻辑失效，最终返回count=0。

问题根源：NOT IN与NULL的诡异交互 #

在SQL中，任何和NULL的比较操作结果都是UNKNOWN（既不是TRUE也不是FALSE）。当你使用NOT IN时，它等价于对所有子查询结果取!=然后做AND逻辑：

users.id NOT IN (val1, val2, NULL) 
等价于 
users.id != val1 AND users.id != val2 AND users.id != NULL

由于users.id != NULL的结果是UNKNOWN，整个AND表达式的结果也会变成UNKNOWN。数据库在过滤行时，会忽略WHERE条件为UNKNOWN的行，最终没有任何行被统计，所以结果是0。

结合你的场景分析 #

你提到SELECT Count(user_id) as totalusers FROM users_roles WHERE role_id IN (10,12)结果是40320，但这里的Count(user_id)会自动忽略user_id为NULL的行。而你的第一条查询的子查询SELECT user_id FROM users_roles WHERE role_id IN (10,12)会包含所有匹配的行——包括那些user_id为NULL的行（虽然有外键约束，但外键列允许存储NULL，只要没有对应的父行即可）。

而你的第二条查询，内层的IN子查询SELECT users.id FROM users WHERE users.id IN (...)只会返回存在于users表中的有效ID（users.id是主键，必然非NULL），所以外层的NOT IN处理的是一个没有NULL的结果集，逻辑正常执行，得到总用户数3190466 - 40320 = 3150136的正确结果。

验证方法 #

你可以执行这条SQL确认子查询中是否存在NULL的user_id：

SELECT user_id FROM users_roles 
WHERE (role_id = 10 OR role_id = 12) 
AND user_id IS NULL;

如果返回了行，就坐实了这个问题。

修复方案 #

有两种可靠的修复方式：

1. 在子查询中排除NULL值：

SELECT count(1) FROM users 
WHERE users.id NOT IN ( 
    SELECT user_id FROM users_roles 
    WHERE (role_id = 10 OR role_id = 12)
    AND user_id IS NOT NULL -- 新增过滤条件
);

2. 使用NOT EXISTS替代NOT IN（更推荐，NULL处理更直观）：

SELECT count(1) FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM users_roles ur
    WHERE ur.user_id = u.id
    AND ur.role_id IN (10, 12)
);

总结 #

NOT IN对NULL的处理是SQL中容易踩的坑，尤其是当子查询涉及外键列（允许NULL）时。优先使用NOT EXISTS可以避免这类意外，同时在性能上也通常和NOT IN相当甚至更优。

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