SQL的EXISTS和NOT EXISTS的目的

Question

我时常看到这些用法，但似乎都可以通过正常的联接或子查询同样好甚至更好地完成。

我认为它们是误导性的（与常规联接和子查询相比，它们很难准确可视化），常常被误解（例如，在 EXISTS / NOT EXISTS 子查询中使用 SELECT * 会表现出与 SELECT 1 相同的行为），在我有限的经验中，执行速度较慢。

有人能描述和/或提供一个最适合它们的示例或者没有其他选择而必须使用它们的情况吗？请注意，由于它们的执行和性能可能与平台相关，我特别关心它们在MySQL中的使用。

- Riedsio

---

我找到了这个链接，比较了exists和in。虽然是针对MS-SQL Server的，但同样的原则也适用于其他数据库：http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx...另一个链接，更加关注性能方面：http://oracle-online-help.blogspot.com/2006/11/in-vs-exist-in-sql.html 还有一个...http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/EXISTSandNOTEXISTSVersusINandNOTIN.htm - FrustratedWithFormsDesigner

我发现在只对单个表中的行感兴趣时，使用 EXISTS 和 NOT EXISTS 要比创建不必要的 JOIN 更清晰。去想象吧。 - Larry Lustig

+1 Larry。确实如此。而且JOIN有一些副作用：https://dev59.com/dnM_5IYBdhLWcg3wNwQv#1474974 - Michael Buen

Answer 1

我时常看到这些内容被使用，但它们似乎都可以通过正常的JOIN或子查询同样甚至更好地完成。

虽然这篇文章涉及SQL Server，但它可能会有你感兴趣的内容：

• IN vs. JOIN vs. EXISTS

简而言之，JOIN是集合操作，而EXISTS是谓词。

换句话说，这些查询：

SELECT  *
FROM    a
JOIN    b
ON      some_condition(a, b)

对比

SELECT  *
FROM    a
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    b
        WHERE   some_condition(a, b)
        )

它们不同：前者可以从a返回多条记录，而后者则不能。

它们的对应物，NOT EXISTS与LEFT JOIN / IS NULL, 在逻辑上是相同的，但在性能上不同。

实际上，在SQL Server中，前者可能更有效：

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

- Quassnoi

Answer 2

如果主查询返回的行数比你想要查找的表少得多。例如：

SELECT st.State
FROM states st
WHERE st.State LIKE 'N%' AND EXISTS(SELECT 1 FROM addresses a WHERE a.State = st.State)

使用连接操作来实现这个功能会慢得多。或者举个更好的例子，如果你想要在多个表中搜索某个项是否存在。

- The Scrum Meister

---

如果你坚持强制使用join顺序，我会认为使用STRAIGHT JOIN会更有效率。对于你的例子，可以这样写：SELECT st.State FROM states st STRAIGHT_JOIN addresses a ON a.State = st.State WHERE st.State LIKE 'N%' - Riedsio

@Riedsio 我不想用 JOIN。假设地址表中每个州都有很多记录。 - The Scrum Meister

我现在明白了。我可以看出如果在EXISTS子句中加入LIMIT 1的话，会有一些改进（或者至少不会有坏处）-- 即... AND EXISTS(SELECT 1 FROM addresses a WHERE a.State = st.State LIMIT 1)。 - Riedsio

Answer 3

在一个UPDATE语句中，你不能[轻松地]使用连接操作，所以WHERE EXISTS非常适用于此：

UPDATE mytable t
   SET columnX = 'SomeValue'
 WHERE EXISTS 
   (SELECT 1 
      FROM myothertable ot
     WHERE ot.columnA = t.columnY
       AND ot.columnB = 'XYX'
   );

编辑：基于Oracle而非MySQL，是的，有使用内联视图的方法，但在我看来这种方式更加简洁。

- BQ.

---

BQ：在MySQL中，我很确定您可以将其编写为UPDATE mytable t JOIN myothertable ot ON ot.columnA = t.columnY SET columnX = 'SomeValue' WHERE ot.columnB = 'XYX'。 - Gabe

这是一个有趣的观察，但我特别关心的是MySQL。 - Riedsio