不等于和不等于之间的区别

My query needs to return all usage records whose pipeline rate is not 'No Usage'.

我的查询需要返回所有的使用记录，它们的管道速率不是“没有使用”。

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

不存在、不存在和左连接之间的区别是什么?

I have seen the above question and decided to use IN over EXISTS as the values in the tables are nullable. Which one of the following is better and more efficient or is there any other way which more efficient than the following two?

我已经看到了上面的问题，并决定使用over exist，因为表中的值是可空的。下面哪一种更好更有效，或者有没有其他比下面两种更有效的方法?

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')

2 个解决方案

#1

NOT IN is going to give you the wrong results if id is nullable (which I hope it is not, otherwise it has a terrible name).

如果id为nullable(我希望它不是，否则它有一个糟糕的名字)，则NOT IN将给您带来错误的结果。

Why would you choose IN over EXISTS when it has been proven time and time again that EXISTS is more efficient (or at least no less efficient), since it can short-circuit? IN has to materialize the entire set.

既然它一再被证明是更有效的(或者至少是同样有效的)，那么为什么要选择“存在”而不是“存在”呢?因为它会短路。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'
  );

You can also express your other query like this:

你也可以这样表达你的另一个问题:

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'
  );

But I have no idea which, if either, gets the correct results. This is why we typically ask for sample data and desired results.

但我也不知道，如果得到了正确的结果。这就是为什么我们通常需要样本数据和期望的结果。

Your use of SELECT * is likely to have a greater negative impact on performance than whether you use IN or EXISTS. FWIW.

您对SELECT *的使用可能会对性能产生比您是否使用IN或是否存在更大的负面影响。就其价值而言。

#2

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

"不存在与不存在之间的区别是什么?"

Not exists exits as soon as it sees one match.

不存在一旦它看到一个匹配。

Not in doesn't. People get concerned about in or not in lists that contain large numbers of results, but some testing by Jeff Moden shows that they work fine up to the million item range, which is usually sufficient.

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

Left join where is null is set based, so it's the "classic" solution. "In" is basically turned into a giant or list. Left join where is null doesn't have any specific advantages when you're just testing for something being missed, I like to use a left self join/is null pattern when I'm looking for say the most recent event per user.

左连接是空的，所以它是“经典”的解决方案。“In”基本上变成了一个巨大的列表。当你只是在测试丢失的东西时，为null的左连接没有任何特别的优势，我喜欢使用左自连接/是空模式当我寻找每个用户最近的事件时。

Not in is super simple, and any novice developer will understand what it does.

Not in是超级简单的，任何初学者都能理解它的作用。

Not exists is almost as clear, but maybe above novice.

不存在几乎是清楚的，但可能高于新手。

Left join/is null is routinely misunderstood even by mid-level developers. So personally I find not in the most maintainable.

即使是中层开发人员也经常误解左连接/是空的。所以我个人认为不是最可维护的。

#1