为何Left Join与Not In查询结果不一致？哪种方法更准确？

结果差异原因 #

差异的核心原因是login_activity表的userid列存在NULL值，这触发了NOT IN的逻辑陷阱：

• 当NOT IN的子查询结果中包含NULL时，SQL会将user_id NOT IN (...)的判断结果视为UNKNOWN（而非TRUE），这类行不会被计入统计。
• 而LEFT JOIN + WHERE a.userid IS NULL的逻辑不受NULL影响：只要all_users的user_id在login_activity中没有匹配到（包括login_activity中存在NULL的情况），都会被正确统计。

你可以验证这一点，执行以下查询查看login_activity中是否存在NULL的userid：

select count(*) from login_activity where userid is null;

这个计数应该刚好等于两个查询的结果差值（约1000）。

方法优劣与准确性对比 #

1. 准确性：

   • LEFT JOIN + WHERE a.userid IS NULL（或等价的NOT EXISTS）更准确，完全避免了NULL值导致的统计遗漏，是这类需求的标准解决方案。
   • NOT IN存在固有缺陷，只要子查询包含NULL就会出错，除非你能100%保证子查询结果无NULL，否则不建议使用。

2. 性能：

   • 在AWS Athena（Presto引擎）中，LEFT JOIN和NOT EXISTS的性能基本相当，引擎会优化为类似的执行计划。
   • 你的NOT IN查询中额外加了DISTINCT，这会增加数据去重的开销，当login_activity数据量较大时，性能会比LEFT JOIN差。如果去掉DISTINCT，NOT IN的性能会接近，但依然存在NULL陷阱问题。

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