NOT IN and equals与IN and not equals的区别

Question

我的查询需要返回流水线速率不是'No Usage‘的所有使用记录。

What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

我已经看到了上面的问题，并决定使用IN而不是EXISTS，因为表中的值可以为空。下面哪一种更好更有效，或者有没有比下面两种更有效的方法？

SELECT *
FROM   usagerecords UR
WHERE  UR.usagerateid NOT IN (SELECT id
                              FROM   pipelinerate PR
                              WHERE  PR.name = 'No Usage')

SELECT *
FROM   usagerecords UR
WHERE  UR.usagerateid IN (SELECT id
                          FROM   pipelinerate PR
                          WHERE  PR.name <> 'No Usage') 

Answer 1

如果id是可空的(我希望它不是空的，否则它的名字很糟糕)，NOT IN会给你错误的结果。

既然一次又一次地证明EXISTS更高效(或者至少不会降低效率)，那么为什么要选择IN而不是EXISTS呢？IN必须实现整个集合。

SELECT * -- stop doing this
  FROM dbo.usagerecords AS UR
  WHERE EXISTS
  (
    SELECT 1 FROM dbo.pipelinerate AS pr
      WHERE pr.id = ur.usagerateid
      AND pr.name <> 'No Usage'
  );

您还可以像这样表达您的其他查询：

SELECT * -- again, stop doing this
  FROM dbo.usagerecords AS UR
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM dbo.pipelinerate AS pr
      WHERE pr.id = ur.usagerateid
      AND pr.name = 'No Usage'
  );

但我不知道哪一个能得到正确的结果。这就是为什么我们通常要求样本数据和期望的结果。

与使用IN或EXISTS相比，使用SELECT *对性能的负面影响可能更大。FWIW。

Answer 2

“NOT EXISTS与NOT IN与LEFT JOIN WHERE IS NULL有什么区别？”

Not exists在发现一个匹配项时立即退出。

人们关心的是包含大量结果的列表中的in或Not，但Jeff Moden的一些测试表明，它们可以很好地工作到百万项范围，这通常是足够的。

Left join is null是基于设置的，所以它是“经典”的解决方案。"In“基本上变成了一个巨大的或列表。Left join where is null没有任何特别的优势当你只是在测试遗漏的东西时，我喜欢使用left self join/is null模式，当我在查找每个用户最近的事件时。

Not in非常简单，任何新手开发人员都会理解它的作用。

Not exists几乎同样清晰，但可能高于新手。

Left join/is null经常被误解，即使是中层开发人员也是如此。所以就我个人而言，我发现它不是最容易维护的。