为什么我的 SQL 'NOT IN' 子句产生与 'NOT EXISTS' 不同的结果 [英] Why is my SQL 'NOT IN' clause producing different results from 'NOT EXISTS'

问题描述

我有两个 SQL 查询产生不同的结果，而我希望它们产生相同的结果.我试图找到没有相应位置的事件数量.所有位置都有一个事件，但事件也可以链接到非位置记录.

以下查询产生的计数为 16244，这是正确的值.

SELECT COUNT(DISTINCT e.event_id)FROM 事件 AS e不存在的地方(SELECT * FROM location AS l WHERE l.event_id = e.event_id)

以下查询产生的计数为 0.

SELECT COUNT(DISTINCT e.event_id)FROM 事件 AS eWHERE e.event_id NOT IN (SELECT l.event_id FROM location AS l)

下面的SQL对数据集做了一些总结

SELECT '事件计数',计数(DISTINCT event_id)FROM 事件联合所有SELECT '位置计数',计数(DISTINCT event_id)发件人地点联合所有SELECT '事件+位置计数',计数(DISTINCT l.event_id)FROM location AS l JOIN events AS e ON l.event_Id = e.event_id

并返回如下结果

<前>事件计数 139599位置计数 123355事件+位置计数 123355

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

解决方案

您在子查询 SELECT l.event_id FROM location AS l 中有一个 NULL，所以 NOT IN 将始终评估为未知并返回 0 结果

SELECT COUNT(DISTINCT e.event_id)FROM 事件 AS eWHERE e.event_id NOT IN (SELECT l.event_id FROM location AS l)

这种行为的原因可以从下面的例子中看出.

<块引用>

'x' NOT IN (NULL,'a','b')

≡ 'x' <> NULL 和 'x' <> 'a' 和 'x'<> 'b'

≡ 未知和真实存在

≡ 未知

I have two SQL queries producing different results when I would expect them to produce the same result. I am trying to find the number of events that do not have a corresponding location. All locations have an event but events can also link to non-location records.

The following query produces a count of 16244, the correct value.

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)    

The following query produces a count of 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)

The following SQL does some summaries of the data set

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

And returns the following results

Event Count         139599
Locations Count         123355
Event+Location Count    123355

Can anyone shed any light on why the 2 initial queries do not produce the same figure.

解决方案

You have a NULL in the subquery SELECT l.event_id FROM locations AS l so 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)

The reason for this behaviour can be seen from the below example.

'x' NOT IN (NULL,'a','b')

≡ 'x' <> NULL and 'x' <> 'a' and 'x' <> 'b'

≡ Unknown and True and True

≡ Unknown

这篇关于为什么我的 SQL 'NOT IN' 子句产生与 'NOT EXISTS' 不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！