SQL NOT IN查询筛选asset_id返回空结果的原因排查

你触发了SQL中NOT IN语法的经典逻辑陷阱，核心原因有两点：

1. NOT IN对NULL值的处理逻辑存在天生缺陷：SQL采用三值逻辑（真/假/未知），任意值与NULL做等值判断（=、!=、IN、NOT IN）时，返回结果永远是「未知」。当NOT IN后的子查询结果集里出现任意一个NULL值时，外层所有行的判断结果都会变成「未知」，最终被数据库过滤，返回空结果。
   直白的逻辑等价示例：x NOT IN (1,2,NULL) 等价于 x != 1 AND x !=2 AND x != NULL，最后一个判断x != NULL结果永远是未知，整个与表达式的结果就是未知，没有任何行能满足判断条件。
2. 你的第一个子查询SELECT asset_id FROM lookup_urls WHERE url_source_id = 1返回结果中，确实存在asset_id为NULL的记录。
   你之前用COUNT(DISTINCT asset_id)做分组统计时没有发现这个问题，是因为所有SQL聚合函数都会自动忽略目标列的NULL值，统计出的7202只是非空asset_id的去重数量，并没有把NULL算进去。
   而你第二个查询能正常返回结果，是因为WHERE url_source_id = 2筛选出的记录里，asset_id列不存在NULL值，NOT IN的判断逻辑可以正常执行。

推荐优先级从高到低：

• 优先使用NOT EXISTS改写，该写法天然规避NULL值问题，语义也更清晰：

SELECT *
FROM lookup_urls t1
WHERE NOT EXISTS (
    SELECT 1
    FROM lookup_urls t2
    WHERE t2.url_source_id = 1
      AND t2.asset_id = t1.asset_id
)

• 如果坚持用NOT IN，必须在子查询中显式过滤NULL值：

SELECT *
FROM lookup_urls 
WHERE asset_id NOT IN (
    SELECT asset_id
    FROM lookup_urls
    WHERE url_source_id = 1
      AND asset_id IS NOT NULL
)

• 也可以用左连接+空值判断的写法，逻辑和NOT EXISTS等价：

SELECT t1.*
FROM lookup_urls t1
LEFT JOIN lookup_urls t2
  ON t1.asset_id = t2.asset_id
  AND t2.url_source_id = 1
WHERE t2.asset_id IS NULL

执行如下SQL即可确认根因，返回结果一定大于0，代表url_source_id=1的分组下确实存在asset_id为NULL的记录：

SELECT COUNT(*)
FROM lookup_urls
WHERE url_source_id = 1
  AND asset_id IS NULL

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