Redshift中使用IN和NOT IN查询结果不符问题求助

这个差值的核心原因是**page_asin字段存在NULL值**，而SQL中NULL的比较逻辑和普通值完全不同，导致这部分NULL行既没有被IN条件包含，也没有被NOT IN条件包含，但在全量查询中被统计了进去。

具体逻辑拆解 #

我们来逐个分析三种情况的行为：

1. 全量查询：所有符合时间范围的行都会被统计，包括page_asin为NULL的行。
2. page_asin IN (subquery)：SQL中，NULL和任何值的比较结果都是UNKNOWN（既不是TRUE也不是FALSE），所以page_asin为NULL的行无法满足IN条件，会被排除在结果外。
3. page_asin NOT IN (subquery)：同样因为NULL的比较特性，NULL NOT IN (...)的结果还是UNKNOWN，而WHERE子句会过滤掉结果为UNKNOWN的行，所以这部分NULL行也会被排除在NOT IN的结果外。

最终，这部分page_asin为NULL的行的video_view总和，就是你看到的约7000的差值。

验证方法 #

你可以执行以下查询来确认这个猜想：

-- 统计全量中page_asin为NULL的video_view总和
SELECT sum(video_view) 
FROM video_metrics 
WHERE start_date BETWEEN TO_DATE('01/26/2018','MM-DD-YYYY') AND TO_DATE('02/04/2018','MM-DD-YYYY') + 0.9999
AND page_asin IS NULL;

这个结果应该正好等于全量结果与前两者之和的差值（约7000）。

修正方案 #

如果需要让IN和NOT IN的结果覆盖全量数据，可以单独处理NULL的情况：

• 对于IN条件，修改为：

  (page_asin IN (SELECT DISTINCT product_asin FROM v2p) OR page_asin IS NULL)
  

• 对于NOT IN条件，修改为：

  (page_asin NOT IN (SELECT DISTINCT product_asin FROM v2p) AND page_asin IS NOT NULL)
  

另外，更推荐用NOT EXISTS替代NOT IN来避免NULL的坑，因为NOT EXISTS的逻辑更直观：

NOT EXISTS (SELECT 1 FROM v2p WHERE v2p.product_asin = video_metrics.page_asin)

当page_asin为NULL时，子查询不会找到匹配行，NOT EXISTS会返回TRUE，这部分行就会被包含在结果中，此时IN和NOT EXISTS的结果之和就会等于全量数据。

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