当我期望两个 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
谁能解释一下为什么两个初始查询没有产生相同的数字。
子查询中有 NULLSELECT l.event_id FROM locations AS l
所以不在将始终评估为未知并返回 0 结果 http://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/
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”
== 未知且真实且真实
== 未知
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)