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

所以...

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。

谁能解释这些结果？

最佳答案

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

要么返回空值:

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
)

或切换到“无效” NOT EXISTS:

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

关于sql - SELECT FROM WHERE X NOT IN Y的意外结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48761744/