sql - SELECT FROM WHERE X NOT IN Y 的意外结果

Question

所以...

select COUNT(*) cnt from docDocument d 
inner join tblAttachment a on d.AttachmentID = a.ID
where
a.ContainerID = 1 

返回 6673

select COUNT(*) from tblAttachment 
where 
ContainerID = 1 

返回 10372

select COUNT(*) cnt from docDocument d 
right join tblAttachment a on d.AttachmentID = a.ID
where
a.ContainerID = 1 
AND
d.ID IS NULL

返回 3699，因为 10372 - 6673 = 3699 是有意义的

SELECT COUNT(*) FROM
(
select ID from tblAttachment a
where 
a.ContainerID = 1
Except
(
    SELECT AttachmentId from docDocument 
)
) tst

不出所料地返回 3699...但是...

select COUNT(*) from tblAttachment a
where 
a.ContainerID = 1 AND
a.ID NOT IN
(
    SELECT d.AttachmentId from docDocument  d
)

我期待它返回 3699，但令人惊讶的是它返回 0。

谁能解释一下这些结果？

Answer 1

如果子查询返回空值，则 NOT IN 不再为真，不会返回任何行。

要返回的不是 null 值:

select COUNT(*) from tblAttachment a
where 
a.ContainerID = 1 AND
a.ID NOT IN
(
    SELECT d.AttachmentId from docDocument  d WHERE d.AttachmentId IS NOT NULL
)

或者切换到“null safe”NOT EXISTS:

select COUNT(*) from tblAttachment a
where 
a.ContainerID = 1 AND
NOT EXISTS
(
    SELECT * from docDocument  d WHERE d.AttachmentId =  a.ID
)