数据库子查询使用[NOT] EXISTS代替[NOT] IN（PE019）

SQL Prompt是一款实用的SQL语法提示工具。SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

在使用子查询比较数据集时，过去曾经是EXISTS逻辑运算符比IN更快。例如，在查询必须执行特定任务的情况下，但仅当子查询返回任何行时，然后在评估WHERE [NOT] EXISTS（子查询）时，数据库引擎只要发现一个就可以退出搜索行，而WHERE [NOT] IN（子查询） 将始终在进一步处理之前从子查询中收集所有结果。

但是，查询优化器现在会尽可能以相同的方式对待EXISTS和IN，因此您不太可能看到任何明显的性能差异。但是，如果子查询的源数据包含NULL值，则在使用NOT IN运算符时需要谨慎。如果是这样，则应考虑使用NOT EXISTS运算符而不是NOT IN，或者将语句重铸为左外部联接。

SQL Prompt（PE019）中的代码分析规则中包含了建议使用[NOT] EXISTS而不是[NOT] IN的建议。

 

哪种效果更好：EXISTS或IN ....？

 

有两种方法可以计算出两个数据集之间的差异，但是最常见的两种方法是使用EXISTS或IN逻辑运算符。想象一下，我们有两个简单的表，一个表包含英语中的所有常用单词（CommonWords），另一个表包含Bram Stoker的“ Dracula”中的所有单词的列表（WordsInDracula）。该TestExistsAndIn下载包括脚本来创建这两个表，并填充和与之相关的文本文件中每一个。通常，在沙盒服务器中拥有这样的表对于在进行开发工作时运行测试很有用，尽管您可以选择使用的书！

在德古拉语中有多少个不常见的单词？假设NULL该CommonWords.Word列中没有值（稍后会详细介绍），则以下查询将返回相同的结果（1555个字），并具有相同的执行计划，这在两个之间使用了合并联接（Right Anti Semi Join）表。

--using NOT IN
SELECT Count(*)
  FROM dbo.WordsInDracula
  WHERE word NOT IN (SELECT CommonWords.word FROM dbo.CommonWords);
 
--Using NOT EXISTS
SELECT Count(*)
  FROM dbo.WordsInDracula
  WHERE NOT EXISTS 
   (SELECT * FROM dbo.CommonWords 
  WHERE CommonWords.word = WordsInDracula.word);

清单1

 

简而言之，SQL Server优化器以相同的方式处理任一查询，它们也将执行相同的查询。

…或任何其他（除内部联接，外部联接或相交之外）？
什么其他所有可能的技术，但是，如使用ANY，EXCEPT，INNER JOIN，OUTER JOIN或INTERSECT？清单2显示了我可以轻松想到的另外七个替代方案，尽管还有其他替代方案。

--using ANY
SELECT Count(*)
  FROM dbo.WordsInDracula 
WHERE NOT(WordsInDracula.word = ANY  
(SELECT word  
    FROM commonwords )) ;  
--Right anti semi merge join
 
--using EXCEPT
SELECT Count(*)
  FROM
    (
    SELECT word
      FROM dbo.WordsInDracula
    EXCEPT
    SELECT word
      FROM dbo.CommonWords
    ) AS JustTheUncommonOnes;
--Right anti semi merge join
 
--using LEFT OUTER JOIN 
SELECT Count(*)
  FROM dbo.WordsInDracula
    LEFT OUTER JOIN dbo.CommonWords
      ON CommonWords.word = WordsinDracula.word
  WHERE CommonWords.word IS NULL;
--right outer merge join
 
--using FULL OUTER JOIN 
SELECT Count(*)
  FROM dbo.WordsInDracula
    full OUTER JOIN dbo.CommonWords
      ON CommonWords.word = WordsinDracula.word
  WHERE CommonWords.word IS NULL;
--Full outer join implemented as a merge join.
 
--using intersect to get the difference
SELECT (SELECT Count(*) FROM WordsInDracula)-Count(*)
  FROM
    (
    SELECT word
      FROM dbo.WordsInDracula
    intersect
    SELECT word
      FROM dbo.CommonWords
    ) AS JustTheUncommonOnes;
--inner merge join
 
--using FULL OUTER JOIN syntax to get the difference
SELECT Count(*)-(SELECT Count(*) FROM CommonWords)
FROM dbo.WordsInDracula
    full OUTER JOIN dbo.CommonWords
      ON CommonWords.word = WordsinDracula.word
--full outer merge join
 
--using INNER JOIN syntax to get the difference
SELECT (SELECT Count(*) FROM WordsinDracula)-Count(*)
FROM dbo.WordsInDracula
    INNER JOIN dbo.CommonWords
      ON CommonWords.word = WordsinDracula.word
--inner merge join

清单2
测试线束

 

所有这9个查询都给出相同的结果，但有没有一种方法的效果更好？让我们将它们全部放入一个简单的测试工具中，看看每个版本需要多长时间！再次，代码下载文件包括测试工具代码以及所有九个查询。

结果表明，尽管查询看起来有很大不同，但对于优化程序而言，它通常只是“语法糖”。无论您的SQL有多优雅，优化器都只会耸耸肩，并提出执行它的有效计划。实际上，前四个都使用完全相同的“正确的半合并合并”执行计划，并且都花费相同的时间。

 

我们将通过多次运行测试来检查差异。该INTERSECT和INNER JOIN查询中都使用内部合并连接，并接近。这两个FULL OUTER JOIN查询稍慢一些，但这是一场激烈的比赛。

 

 

NOT IN的陷阱

 

比较具有空值的集合存在一定的不现实性，但是如果在每天的数据库报告热中发生这种情况，则可能会出错。如果NULL子查询或表达式的结果中有一个值传递给IN逻辑运算符，则它将给出合理的响应，并且与等效值相同EXISTS。但是，NOT IN行为却大不相同。

清单3演示了这个问题。我们在@someWord表变量中插入三个常用词和三个不常用词，并且我们想知道不在表变量中的常用词的数量。

SET NOCOUNT ON;
DECLARE @someWord TABLE
(
    word NVARCHAR(35) NULL
);
INSERT INTO @someWord
(
    word
)
--three common words
SELECT TOP 3
       word
FROM dbo.commonwords
ORDER BY word DESC;
 
-- three uncommon words
INSERT INTO @someWord
(
    word
)
VALUES
('flibberty'),
('jibberty'),
('flob');
 
SELECT [NOT EXISTS without NULL] = COUNT(*)
FROM commonwords AS MyWords
WHERE NOT EXISTS
(
    SELECT word FROM @someWord AS s WHERE s.word LIKE MyWords.word
);
 
SELECT [NOT IN without NULL] = COUNT(*)
FROM commonwords AS MyWords
WHERE word NOT IN (
                      SELECT word FROM @someWord
                  );
 
--Insert a NULL value
INSERT INTO @someWord
(
    word
)
VALUES
(NULL);
 
SELECT [NOT EXISTS with NULL] = COUNT(*)
FROM commonwords AS MyWords
WHERE NOT EXISTS
(
    SELECT word FROM @someWord AS s WHERE s.word LIKE MyWords.word
);
SELECT [NOT IN with NULL] = COUNT(*)
FROM commonwords AS MyWords
WHERE word NOT IN (
                      SELECT word FROM @someWord
                  );

清单3

 

在NOT IN查询时，才插入NULL到@someword，并且两个NOT EXISTS查询，所有正确地告诉我们，60385点的话是不是在我们的表变量，因为三都，并有在所有60388个常用词。但是，如果子查询可以返回NULL，则NOT IN根本不返回任何行。

 

NULL真正的意思是“未知”而不是什么，这就是为什么任何与NULL值比较的表达式都会返回NULL或未知。

 

从逻辑上讲，SQL Server评估子查询，将其替换为其返回的值列表，然后评估[NOT] IN条件。对于IN我们查询的变体，这不会引起问题，因为它可以解决以下问题：

WHERE word = 'flibberty' OR word = 'jibberty' OR word = 'flob'
     OR word = 'zygotes' OR word = 'zygote' OR word = 'zydeco'
     OR word = NULL;

对于“ z…”字样的匹配项，将返回3行。附带了刺NOT IN，它可以解决以下问题：

WHERE word <> 'flibberty' AND word <> 'jibberty'AND word <> 'flob'
    AND word <> 'zygotes' AND word <> 'zygote' AND word <> 'zydeco'
    AND word <> NULL;

AND具有要比较的条件的结果NULL为'unknown'，因此表达式将始终返回零行。这不是错误；这是设计使然。您可以辩称，NULL不应在要使用NOT IN表达式的任何列中使用a ，但是在我们的实际工作中，这些东西可能会渗入表源。值得谨慎。因此，请使用EXISTS变体或其他变体，或始终记住WHERE在IN条件中包含一个从句以消除NULLs。