在第二张表中查找ID存在的行的最有效方法是什么？

Question

我想从一个表中选择所有记录，其中ID存在于第二个表中。

以下两个查询返回正确的结果：

查询1：

SELECT *
FROM Table1 t1
WHERE EXISTS (SELECT 1 FROM Table2 t2 WHERE t1.ID = t2.ID)

查询2:

SELECT *
FROM Table1 t1
WHERE t1.ID IN (SELECT t2.ID FROM Table2 t2)

这些查询中的任何一个是否比另一个更有效？我应该使用其中一个而不是另一个吗？是否有第三种我没想到的方法甚至更有效？

- Tot Zam

---

使用 inner join，不需要子查询。 - Marc B

使用JOIN语句时，会从table2返回列，并在存在多个匹配项时返回多行。它与EXISTS或IN不可互换。 - D Stanley

@MarcB 离开了，不会修复多行。 - paparazzo

@TotZam半连接执行计划操作符。尽管这并不是保证的。有时它会去除重复项并执行内连接，但我不认为使用in还是exists会对选择产生任何影响。然而，对于NOT IN和NOT EXISTS来说，情况并非如此，因为它们可能具有不同的语义和性能。 - Martin Smith

这是一篇关于此主题的好文章。http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/ - Sean Lange

Answer 1

概要：

在所有场景中，IN和EXISTS的表现都相似。以下是用于验证的参数。

执行成本，时间：
两者相同，优化器生成相同的计划。
内存授予：
这两个查询的内存授予相同。
CPU时间，逻辑读取：
Exists在CPU时间方面似乎比IN稍微好一点，尽管读取是相同的。

我对每个查询使用了以下测试数据集进行了10次运行。

1. 非常大的子查询结果集（100000行）
2. 重复行
3. 空值行

对于上述所有情况，IN和EXISTS的表现方式都相同。

关于用于测试的性能V3数据库的一些信息。 有1000000个订单的20000个客户，因此每个客户在订单表中随机重复（在10到100范围内）。

执行成本，时间：
以下是两个查询运行的屏幕截图。观察每个查询的相对成本。

内存成本：
这两个查询的内存授予也是相同的。我强制使用MDOP 1，以便不将它们溢出到TEMPDB。

CPU时间，读取：

对于Exists：

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 3855, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 469 ms,  elapsed time = 595 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

对于IN：

(20000 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 3855, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 547 ms,  elapsed time = 669 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

在每种情况下，优化器都足够智能，可以重新排列查询语句。

我倾向于仅使用“EXISTS”(我的意见)。使用“EXISTS”的一个用例是当您不想返回第二个表结果集时。

根据Martin Smith的查询更新：

我运行了下面的查询，以找到从第一个表中获取存在于第二个表中引用的行的最有效方式。

SELECT DISTINCT c.*
FROM Customers c
JOIN Orders o ON o.custid = c.custid   

SELECT c.*
FROM Customers c
INNER JOIN (SELECT DISTINCT custid FROM Orders) AS o ON o.custid = c.custid

SELECT *
FROM Customers C
WHERE EXISTS(SELECT 1 FROM Orders o WHERE o.custid = c.custid)

SELECT *
FROM Customers c
WHERE custid IN (SELECT custid FROM Orders)

所有上述查询的成本大体相同，除了第二个INNER JOIN，其计划与其他查询相同。 < p >

内存授予：
此查询

SELECT DISTINCT c.*
FROM Customers c
JOIN Orders o ON o.custid = c.custid 

所需的内存授权

此查询

SELECT c.*
FROM Customers c
INNER JOIN (SELECT DISTINCT custid FROM Orders) AS o ON o.custid = c.custid 

所需的内存授予量为...

CPU时间、读取次数：
对于查询：

SELECT DISTINCT c.*
FROM Customers c
JOIN Orders o ON o.custid = c.custid   

(20000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 48, logical reads 1344, physical reads 96, read-ahead reads 1248, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 5, logical reads 3929, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 5, logical reads 322, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1453 ms,  elapsed time = 781 ms.

查询：

SELECT c.*
FROM Customers c
INNER JOIN (SELECT DISTINCT custid FROM Orders) AS o ON o.custid = c.custid

(20000 row(s) affected)
Table 'Customers'. Scan count 5, logical reads 322, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 5, logical reads 3929, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1499 ms,  elapsed time = 403 ms.

- TheGameiswar

---

以下是您可能考虑添加的另外两个查询（我预计它们会更糟）：SELECT DISTINCT c.* FROM Customers c JOIN orders o ON o.custid = c.custid 和 SELECT c.* FROM Customers c INNER JOIN (SELECT DISTINCT custid FROM orders) AS o ON o.custid = c.custid 。 - Martin Smith

实际上，我刚刚下载了那个数据库并进行了测试，它也成功地将那些额外的“DISTINCT”查询之一转换为半连接。 - Martin Smith

谢谢Martin，我正要发布结果，除了第二个内连接外，所有其他连接的成本都相同。 - TheGameiswar

我们仍然会发布结果。评论是短暂的等等。 - Martin Smith

我在原始问题中没有提到的一件事，但我认为值得注意的是，DISTINCT不适用于“text”数据类型的字段，因此即使JOIN的解决方案在效率上相等，它们可能无法作为替代品使用。 - Tot Zam

Answer 2

在纯SQL语法中，它们的含义完全相同，因此哪个更“有效”取决于编译器构建计划和引擎获取数据的方式。唯一确定的方法是两种方式都运行并比较结果。即使如此，差异也仅适用于该上下文，这意味着在其他情况下可能有其他更快的方法。

- D Stanley

---

我经常使用类似的EXIST和IN查询，因此我更想知道是否有一般规则来确定哪种方法最好，而不是针对一个特定情况。 - Tot Zam

@TotZam 并不是真的 - 一些引擎可能会将其中一个转换为更好的计划，但从一般的 SQL 视角来看，在你的例子中并没有区别。 - D Stanley

Answer 3

一般来说，两者差不多，但是当一个更好时，我会选择它（对我而言）

如果t1.id是唯一的，那么你可以直接进行连接操作

SELECT t1.*
FROM Table1 t1
JOIN Table2 t2 
ON t1.ID = t2.ID 

即使 t2.ID 不是唯一的，而且您只想要唯一的行，那么您可以这样做：

SELECT distinct t1.*
FROM Table1 t1
JOIN Table2 t2 
ON t1.ID = t2.ID 

- paparazzo

---

使用JOIN加上DISTINCT比EXISTS或IN更高效吗？ - Tot Zam

@TotZam 为什么不在你自己的数据上进行测试呢？ - paparazzo

在我的原始问题中我没有提到的一些事情，但我认为值得注意的是，DISTINCT不能很好地处理“文本”数据类型的字段，因此即使这个解决方案在效率上等同，这也是为什么JOIN可能不是一个替代方案的另一个原因。 - Tot Zam

@TotZam 第二次了。在你的数据上进行测试。 - paparazzo

我经常使用类似的EXIST和IN查询，因此我更想知道是否有一般规则来确定哪种方法最好，而不是针对一个特定情况。我确实进行了一些测试，到目前为止还没有看到任何区别，并想知道这是否总是如此。 - Tot Zam

第三次效率会因数据而异，因此您需要使用您自己的数据进行测试。选择最佳选项可能很少见，但这是一个选项。 - paparazzo