何时在Transact SQL中使用EXCEPT而不是NOT EXISTS？

Question

我最近才了解到SQL Server中的新“EXCEPT”子句（有点晚，我知道...）是通过阅读同事编写的代码发现的。它真的让我感到惊奇！

但是我有一些关于它的使用问题：何时建议使用它？与使用“AND NOT EXISTS…”的相关查询相比，性能上是否有区别？

在阅读BOL中EXCEPT的文章后，我认为它只是第二个选项的简写，但当我重写了几个查询并检查了执行计划时 - 惊喜！ EXCEPT版本的执行计划更短，执行速度也更快。这总是这样吗？

因此，我想知道：使用这个强大工具的指导方针是什么？

- Joe Pineda

---

好的参考：http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ - OMG Ponies

@rexem：你的参考资料从未提到EXCEPT子句... - Cᴏʀʏ

不是，但它确实谈到了NOT EXISTS的性能问题。该网站还是有关数据库性能相关问题的优秀参考资料。 - OMG Ponies

Answer 1

EXCEPT将NULL值视为匹配。

此查询：

WITH    q (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  1
        ),
        p (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    q
WHERE   value NOT IN
        (
        SELECT  value
        FROM    p
        )

将返回一个空行集。

此查询：

WITH    q (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  1
        ),
        p (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    q
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    p
        WHERE   p.value = q.value
        )

将返回

NULL
1

和这个：

WITH    q (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  1
        ),
        p (value) AS
        (
        SELECT  NULL
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    q
EXCEPT
SELECT  *
FROM    p

将返回：

1

在递归公共表达式（CTE）的EXCEPT子句中，也允许使用递归引用。尽管它的行为方式有些奇怪：它返回前一个集合的最后一行之外的所有内容，而不是除了整个前一个集合之外的所有内容:

WITH    q (value) AS
        (
        SELECT  1
        UNION ALL
        SELECT  2
        UNION ALL
        SELECT  3
        ),
        rec (value) AS
        (
        SELECT  value
        FROM    q
        UNION ALL
        SELECT  *
        FROM    (
                SELECT  value
                FROM    q
                EXCEPT
                SELECT  value
                FROM    rec
                ) q2
        )
SELECT  TOP 10 *
FROM    rec

---
1
2
3
-- original set
1
2
-- everything except the last row of the previous set, that is 3
1
3
-- everything except the last row of the previous set, that is 2
1
2
-- everything except the last row of the previous set, that is 3, etc.
1

SQL Server开发人员可能只是忘记禁止它了。

- Quassnoi

---

同时，except 返回一个集合，因此具有不同的值。 - Magnus

Answer 2

我对 except、not exists、not in 和 left outer join 进行了很多分析。通常，left outer join 是查找缺失行最快的方法，特别是在以主键为依据进行连接时。如果您知道返回的选择结果列表很小，那么 Not In 可能非常快。

我经常使用 EXCEPT 来比较重写代码时返回的内容。先运行旧代码并保存结果，然后再运行新代码并保存结果，最后使用 except 捕获所有差异。这是一种非常快速和简单的查找差异的方法，特别是需要获取包括 null 在内的所有差异时。它非常适合在编码过程中进行快速易用的比较。

但是，每个情况都是不同的。我向我指导过的每个开发者都说过：试试看。用各种不同的方式计时。试试看，计时，实现。

- Linda Warner

Answer 3

EXCEPT用于比较两个完整的SELECT语句中的所有(成对的)列。 NOT EXISTS用于根据WHERE子查询中指定的条件比较两个或多个表。

可以使用NOT EXISTS来重写EXCEPT。(可以使用ROW_NUMBER和NOT EXISTS来重写EXCEPT ALL。)

此信息来源于这里

- priyanka.sarkar

Answer 4

SQL服务器的执行计划是不可预测的。在我的经验中，当出现性能问题时，用户通常会觉得某些语法比其他语法更好，但这种情况纯属随意（我相信算法编写者会明白其中原因）。在这种情况下，查询参数的比较使SQL能够找到一条它无法从直接选择语句中找到的捷径。我相信这是算法的一个缺陷。换句话说，你可以逻辑上推断同样的事情，但是算法在exists查询上没有进行这种转化。有时候这是因为一个能够可靠地解决这个问题的算法需要的执行时间比查询本身还要长，或者至少算法设计者这么认为。

- Yishai

Answer 5

如果您的查询已经优化了，那么使用EXCEPT子句和NOT EXIST / NOT IN之间没有性能差异。第一次运行EXCEPT时，我将相关查询转换为它后感到惊讶，因为它只用了7秒就返回了结果，而相关查询需要22秒。然后我在相关查询中使用了DISTINCT子句并重新运行，它也在7秒内返回了结果。因此，如果您不知道或没有时间优化查询，那么使用EXCEPT很好，否则两者在性能方面是相同的。

- Adil Aleem