何时在 Transact SQL 中使用 EXCEPT 而不是 NOT EXISTS？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1662902/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
提问by Joe Pineda
I just recently learned of the existence of the new "EXCEPT" clause in SQL Server (a bit late, I know...) thru reading code written by a coworker. It truly amazed me!
我最近刚刚通过阅读同事编写的代码了解到 SQL Server 中存在新的“EXCEPT”子句（有点晚了，我知道......）。真的让我很惊喜！
But then I have some questions regarding its usage: when is it recommended to be employed? Is there a difference, performance-wise, between using it versus a correlated query employing "AND NOT EXISTS..."?
但是我对它的使用有一些疑问：建议何时使用？使用它与使用“AND NOT EXISTS ...”的相关查询在性能方面有区别吗？
After reading EXCEPT's article in the BOL I thought it was just a shorthand for the second option, but was surprised when I rewrote a couple queries using it (so they had the "AND NOT EXISTS" syntax much more familiar to me) and then checked the execution plans - surprise! The EXCEPT version had a shorter execution plan, and executed faster, also. Is this always so?
在阅读了 BOL 中 EXCEPT 的文章后，我认为这只是第二个选项的简写，但是当我使用它重写了几个查询时感到惊讶（所以他们对我来说更熟悉“AND NOT EXISTS”语法）然后检查执行计划 - 惊喜！EXCEPT 版本的执行计划更短，执行速度也更快。总是这样吗？
So I'd like to know: what are the guidelines for using this powerful tool?
所以我想知道：使用这个强大工具的准则是什么？
回答by Quassnoi
EXCEPTtreats NULLvalues as matching.
EXCEPT将NULL值视为匹配。
This query:
这个查询：
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
)
will return an empty rowset.
将返回一个空行集。
This query:
这个查询：
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
)
will return
将返回
NULL
1
, and this one:
， 和这个：
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
will return:
将返回：
1
Recursive reference is also allowed in EXCEPTclause in a recursive CTE, though it behaves in a strange way: it returns everything except the last rowof a previous set, not everything except the whole previous set:
EXCEPT在 recursive 的子句中也允许递归引用CTE，尽管它的行为方式很奇怪：它返回除前一组的最后一行之外的所有内容，而不是除整个前一组之外的所有内容：
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 Serverdevelopers must just have forgotten to forbid it.
SQL Server开发人员一定只是忘记了禁止它。
回答by Linda Warner
I have done a lot of analysis of except, not exists, not in and left outer join. Generally the left outer join is the fastest for finding missing rows, especially joining on a primary key. Not In can be very fast if you know it will be a small list returned in the select.
我对except、notexists、notin和leftouter join做了很多分析。通常，左外连接是查找丢失行的最快方法，尤其是在主键上连接。如果您知道它会在选择中返回一个小列表，则 Not In 可能会非常快。
I use EXCEPT a lot to compare what is being returned when rewriting code. Run the old code saving results. Run new code saving results and then use except to capture all differences. It is a very quick and easy way to find differences, especially when needing to get all differences including null. Very good for on the fly easy coding.
我经常使用 EXCEPT 来比较重写代码时返回的内容。运行旧代码保存结果。运行新的代码保存结果，然后使用 except 来捕获所有差异。这是查找差异的一种非常快速和简单的方法，尤其是在需要获取包括 null 在内的所有差异时。非常适合即时轻松编码。
But, every situation is different. I say to every developer I have ever mentored. Try it. Do timings all different ways. Try it, time it, do it.
但是，每种情况都不同。我对我指导过的每个开发人员说。尝试一下。以不同的方式计时。尝试，计时，做。
回答by priyanka.sarkar
EXCEPT compares all (paired)columns of two full-selects. NOT EXISTS compares two or more tables accoding to the conditions specified in WHERE clause in the sub-query following NOT EXISTS keyword.
EXCEPT 比较两个全选的所有（成对）列。NOT EXISTS 根据 NOT EXISTS 关键字后的子查询中的 WHERE 子句中指定的条件比较两个或多个表。
EXCEPT can be rewritten by using NOT EXISTS. (EXCEPT ALL can be rewritten by using ROW_NUMBER and NOT EXISTS.)
EXCEPT 可以使用 NOT EXISTS 重写。（EXCEPT ALL 可以使用 ROW_NUMBER 和 NOT EXISTS 重写。）
Got this from here
从这里得到这个
回答by Yishai
There is no accounting for SQL server's execution plans. I have always found when having performance issues that it was utterly arbitrary (from a user's perspective, I'm sure the algorithm writers would understand why) when one syntax made a better execution plan rather than another.
不考虑 SQL 服务器的执行计划。我总是发现在遇到性能问题时，当一种语法制定更好的执行计划而不是另一种语法时，它完全是任意的（从用户的角度来看，我相信算法编写者会理解为什么）。
In this case, something about the query parameter comparison allows SQL to figure out a shortcut that it couldn't from a straight select statement. I'm sure that is a deficiency in the algorithm. In other words, you could logically interpolate the same thing, but the algorithm doesn't make that translation on an exists query. Sometimes that is because an algorithm that could reliably figure it out would take longer to execute than the query itself, or at least the algorithm designer thought so.
在这种情况下，有关查询参数比较的某些内容允许 SQL 找出它无法从直接选择语句中找到的快捷方式。我确定这是算法中的一个缺陷。换句话说，您可以在逻辑上插入相同的内容，但算法不会对存在的查询进行转换。有时这是因为能够可靠地计算出来的算法执行时间比查询本身要长，或者至少算法设计者是这么认为的。
回答by Adil Aleem
If your query is fine tuned then there is no performance difference b/w using of EXCEPT clause and NOT EXIST/NOT IN.. first time when I ran EXCEPT after changing my correlated query into it.. I was surprised because it returned with the result just in 7 secs while correlated query was returning in 22 secs.. then I used distinct clause in my correlated query and reran.. it also returned in 7 secs.. so EXCEPT is good when you don't know or don't have time to fine tuned your query otherwise both are same performance wise..
如果您的查询经过微调，那么使用 EXCEPT 子句和 NOT EXIST/NOT IN 时没有性能差异 b/w 。当我第一次在将相关查询更改为 EXCEPT 后运行 EXCEPT 时.. 我很惊讶，因为它返回了结果仅在 7 秒内，而相关查询在 22 秒内返回.. 然后我在相关查询中使用了不同的子句并重新运行.. 它也在 7 秒内返回.. 所以当你不知道或不知道时 EXCEPT 很好有时间对您的查询进行微调，否则两者的性能都相同..
