NOT IN 不会产生与 NOT EXISTS 相同的结果 [英] NOT IN does not produce same results as NOT EXISTS

问题描述

这些是相当基本的陈述.我有一个链接到另一个表中项目的图形列表.我想看看有多少图形没有使用，理论上可以删除.

These are rather basic statements. I have a list of graphics which are linked to items in another table. I want to check how many of the graphics are not in use and can theoretically be deleted.

所以首先我使用了 NOT IN 子句:

So first I used the NOT IN clause:

SELECT [GraphicNr]
      ,[Graphicfile]
  FROM [dbo].[Graphic]
 WHERE graphicnr NOT IN (SELECT graphicnr FROM dbo.Komp)

结果为零，这对我来说似乎很奇怪.将其重写为 NOT EXISTS 后，我得到了大约 600 个结果:

Which gave zero results, which seemed weird to me. After rewriting it to a NOT EXISTS, I got about 600 results:

SELECT [GraphicNr]
      ,[Graphicfile]
  FROM [dbo].[Graphic] a
 WHERE NOT EXISTS (SELECT graphicnr FROM dbo.komp b WHERE a.GraphicNr = b.GraphicNr)

所以我想我真的没有问题，因为第二个语句有效，但据我了解，第一个语句不应该给出相同的结果吗?

So I guess I don't really have a problem, since the second statement works, but to my understanding, shouldn't the first one give the same results?

推荐答案

NOT IN 与子查询有奇怪的行为.如果子查询中的任何行返回 NULL 值，则返回 没有 行.这是由于遵循了 NULL 的严格语义(这意味着:我不知道它们是否相等").

NOT IN with a subquery has strange behavior. If any row in the subquery returns a NULL value, then no rows are returned. This is due to following the strict semantics of NULL (which means: "I don't know if they are equal").

NOT EXISTS 的行为与您预期的一样.出于这个原因，我建议不要在子查询中使用 NOT IN.始终使用 NOT EXISTS.

NOT EXISTS behaves as you would expect. For this reason, I recommend never using NOT IN with a subquery. Always use NOT EXISTS.

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