存在（EXISTS）与连接（JOIN）的区别及存在子句的使用

Question

以下是代码示例：

CREATE TABLE #titles(
    title_id       varchar(20),
    title          varchar(80)       NOT NULL,
    type           char(12)          NOT NULL,
    pub_id         char(4)               NULL,
    price          money                 NULL,
    advance        money                 NULL,
    royalty        int                   NULL,
    ytd_sales      int                   NULL,
    notes          varchar(200)          NULL,
    pubdate        datetime          NOT NULL
 )
 GO

 insert #titles values ('1', 'Secrets',   'popular_comp', '1389', $20.00, $8000.00, 10, 4095,'Note 1','06/12/94')
 insert #titles values ('2', 'The',       'business',     '1389', $19.99, $5000.00, 10, 4095,'Note 2','06/12/91')
 insert #titles values ('3', 'Emotional', 'psychology',   '0736', $7.99,  $4000.00, 10, 3336,'Note 3','06/12/91')
 insert #titles values ('4', 'Prolonged', 'psychology',   '0736', $19.99, $2000.00, 10, 4072,'Note 4','06/12/91')
 insert #titles values ('5', 'With',      'business',     '1389', $11.95, $5000.00, 10, 3876,'Note 5','06/09/91')
 insert #titles values ('6', 'Valley',    'mod_cook',     '0877', $19.99, $0.00,    12, 2032,'Note 6','06/09/91')
 insert #titles values ('7', 'Any?',      'trad_cook',    '0877', $14.99, $8000.00, 10, 4095,'Note 7','06/12/91')
 insert #titles values ('8', 'Fifty',     'trad_cook',    '0877', $11.95, $4000.00, 14, 1509,'Note 8','06/12/91')
 GO


CREATE TABLE #sales(
    stor_id        char(4)           NOT NULL,
    ord_num        varchar(20)       NOT NULL,
    ord_date       datetime          NOT NULL,
    qty            smallint          NOT NULL,
    payterms       varchar(12)       NOT NULL,
    title_id       varchar(80)
)
 GO
insert #sales values('1', 'QA7442.3', '09/13/94', 75, 'ON Billing','1')
insert #sales values('2', 'D4482',    '09/14/94', 10, 'Net 60',    '1')
insert #sales values('3', 'N914008',  '09/14/94', 20, 'Net 30',    '2')
insert #sales values('4', 'N914014',  '09/14/94', 25, 'Net 30',    '3')
insert #sales values('5', '423LL922', '09/14/94', 15, 'ON Billing','3')
insert #sales values('6', '423LL930', '09/14/94', 10, 'ON Billing','2')


SELECT    title, price
FROM      #titles
WHERE     EXISTS
(SELECT   *
FROM      #sales
WHERE     #sales.title_id = #titles.title_id
AND       qty >30)


SELECT    t.title, t.price
FROM     #titles t
inner join #sales s on t.title_id = s.title_id
where s.qty >30 

我想知道上述两个查询之间的区别，它们都会产生相同的结果。我还想知道 EXISTS 关键字的作用以及在哪里使用？

- satyajit

---

我建议在您的exists查询中使用SELECT NULL，因为实际上不需要花时间将值提取到结果集中。我不知道SQL Server是否允许使用SELECT NULL，如果不允许，您可以选择SELECT 1或类似的小值。 - corsiKa

在SQL Server中，SELECT NULL、SELECT 1或者SELECT <20个字段>之间没有区别。它们仅仅是检索表格并筛选数据的方式。 - JNK

请注意，带有外部引用的 EXISTS 是一个连接操作，而不仅仅是一个 子句。它是一个 半连接（NOT EXISTS 是一个 反半连接）。此外，您可以使用 EXISTS 来连接表格，例如 Customer C JOIN OrderCategory OC ON EXISTS (SELECT 1 FROM Order O WHERE C.CustomerID = O.CustomerID AND OC.OrderCategoryID = O.OrdercategoryID)。 - ErikE

@ErikE，你没有使用EXISTS来连接表，你只是将EXISTS作为连接表的条件，这与在WHERE中使用它的方式没有任何区别。 - corsiKa

@glowcoder，你能否重写我的示例查询（通过中间的多对多表连接两个表），不使用EXISTS，并且性能至少与之相当？它将如何在WHERE子句中工作（是可能的，但会不太清晰，看起来像旧式的非ANSI连接，但我想看看你的答案）。带有外部引用的EXISTS就是一种连接。看一下执行计划。 - ErikE

Answer 1

EXISTS用于返回一个布尔值，JOIN则返回另外一张完整的表格。

EXISTS仅用于测试子查询是否返回结果，并且一旦有结果就立即停止查询。JOIN用于通过与另一个具有关联的表格中的其他字段组合来扩展结果集。

在您的示例中，这两个查询在语义上是等价的。

通常情况下，当满足以下条件时，请使用EXISTS：

• 您不需要从相关表返回数据
• 相关表中存在重复项（如果值重复，则JOIN可能会导致重复行）
• 您想要检查存在性（使用它来代替LEFT OUTER JOIN...NULL条件）

如果您有适当的索引，则大多数情况下EXISTS的性能将与JOIN相同。例外是在非常复杂的子查询中，使用EXISTS通常更快。

如果您的JOIN键未索引，则使用EXISTS可能更快，但您需要根据特定情况进行测试。

JOIN语法通常更易于阅读和更清晰。

- JNK

---

非常有用的答案，不过我想知道是否值得提到 GROUP BY？JOIN 的一个重大缺点是当它使结果乘以倍数并需要分组以再次减少它们时；如果您可以使用 EXISTS 替换，并且存在适当的键，则在不实际需要任何聚合函数（AVG、SUM 等）的情况下，这通常比分组更快。 - Haravikk

Answer 2

• EXISTS 是半连接
• JOIN 是连接

所以对于3行和5行匹配的情况：

• JOIN 返回15行
• EXISTS 返回3行

这是其他人提到的“短路”效应的结果，并且在使用JOIN时不需要使用DISTINCT。当查找1:n关系中n侧的行的存在时，EXISTS 几乎总是比较快的。

- gbn

---

可以举个例子来演示吗？这会对我这个初学者非常有帮助。@gbn - Vishwanath Dalvi

Answer 3

EXISTS 主要用于快速检索。优化器一旦发现条件成立就会退出，因此可能不需要扫描整个表（在现代版本的SQL Server中，这种优化也适用于 IN()，尽管这并不总是正确的）。这种行为可以因查询而异，在某些情况下，连接实际上可能会给优化器更多的机会来完成其工作。因此，我认为很难说“这是您应该使用 EXISTS 的时候，这是您不应该使用的时候”，因为像许多事情一样，“它取决于具体情况”。

话虽如此，在这种情况下，由于两个表之间基本上是1:1的匹配，你不太可能看到任何性能差异，优化器很可能会生成类似或者完全相同的计划。如果您将每个标题添加50000行并比较连接/exists在销售表上的运行，您可能会看到不同的结果（别忘了您需要更改连接查询以删除重复项、聚合等）。

- Aaron Bertrand

---

“+1 for 'It depends'（这取决于情况）”。最近我一直在进行很多代码调整，并根据我的信念，用“EXISTS”替换“JOIN”条件，因为我认为这样会更快。但实际测试表明，在大多数情况下两者都是相同的（我认为是因为“JOIN”始终基于集群键）。 - JNK

Answer 4

当我需要根据某些行与其他行的交互来排除它们时，我发现exists非常有用。

例如：

SELECT * 
  FROM TABLE a
 WHERE a.val IN (1,2,3)
   AND NOT EXISTS(SELECT NULL
                    FROM TABLE b
                   WHERE b.id = a.id
                     AND b.val NOT IN (1, 2, 3))

在这种情况下，我正在基于具有相同ID但无效的b记录来排除我的a查询中的一行。

实际上，这来自于我在工作中遇到的一个生产问题。该查询将大部分排除逻辑放在了查询中，而不是应用程序中，将加载时间从超过24秒缩短到少于2秒。 :-)

- corsiKa