为什么我的SQL'NOT IN'子句产生与'NOT EXISTS'不同的结果

Question

我有两个SQL查询产生不同的结果，当我期望他们产生相同的结果时。我试图找到没有相应位置的事件数量。所有地点都有一个事件，但事件也可以链接到非地点记录。

以下查询产生的计数为16244，正确的值。

SELECT COUNT(DISTINCT e.event_id) 
FROM events AS e 
WHERE NOT EXISTS 
    (SELECT * FROM locations AS l WHERE l.event_id = e.event_id)  

以下查询将产生的0

SELECT COUNT(DISTINCT e.event_id) 
FROM events AS e 
WHERE e.event_id NOT IN (SELECT l.event_id FROM locations AS l) 

计数下面的SQL做一些数据的汇总设置

SELECT 'Event Count', 
     COUNT(DISTINCT event_id) 
     FROM events 

UNION ALL 

SELECT 'Locations Count', 
     COUNT(DISTINCT event_id) 
     FROM locations 

UNION ALL 

SELECT 'Event+Location Count', 
     COUNT(DISTINCT l.event_id) 
     FROM locations AS l JOIN events AS e ON l.event_Id = e.event_id 

，并返回结果如下

 
Event Count   139599 
Locations Count   123355 
Event+Location Count 123355 

任何人都可以解释为什么2个初始查询不会产生相同的数字。

Answer 1

您在子查询SELECT l.event_id FROM locations AS l有一个NULL那么NOT IN will always evaluate to unknown and return 0 results

SELECT COUNT(DISTINCT e.event_id) 
FROM events AS e 
WHERE e.event_id NOT IN (SELECT l.event_id FROM locations AS l) 

这样做的原因的行为可以从下面的例子可以看出。

'X' NOT IN（NULL， 'A'， 'B'）

≡的 'x' <> NULL和 'X' <> 'a' 和 'X' <>' b”

≡未知和真时真

≡未知

Answer 2

对于NULL，NOT IN表单的工作方式不同。单个NULL的存在将导致整个语句失败，从而不会返回任何结果。

因此，您至少有一个event_id在locations即NULL。

此外，您的查询可能更好的写法如下联接：

SELECT 
    COUNT(DISTINCT e.event_id)  
FROM 
    events AS e 
    LEFT JOIN locations AS l ON e.event_id = l.event_id 
WHERE 
    l.event_id IS NULL 

[更新：显然，NOT EXISTS版本速度更快。]

Answer 3

在和存在处理方式非常不同。

Select * from T1 where x in (select y from T2) 

通常处理为：

select * 
    from t1, (select distinct y from t2) t2 
where t1.x = t2.y; 

子查询被评估，distinct'ed，索引（或散列或排序），然后加入到 原始表 - 通常。

与之相对

select * from t1 where exists (select null from t2 where y = x) 

也就是说更像处理：

for x in (select * from t1) 
    loop 
     if (exists (select null from t2 where y = x.x) 
     then 
     OUTPUT THE RECORD 
     end if 
    end loop 

它总是导致T1的完全扫描而第一查询可以利用一个索引 对T1（ X）。