尝试理解 SQL 查询中的 "except all"。

Question

我看到了这个例子，但我不明白它的意思。

(SELECT drinker FROM Frequents)
     EXCEPT ALL
(SELECT drinker FROM Likes);

关系: 经常到(drinker, bar)，喜欢(drinker, beer)

在这种情况下，ALL是什么作用？结果与下面的查询有何不同？

(SELECT drinker FROM Frequents)
     EXCEPT
(SELECT drinker FROM Likes);

- user234159

---

请看这个链接：http://explainextended.com/2010/02/10/sql-server-except-all/ 该链接介绍了SQL Server中的"EXCEPT ALL"语法，它可以让查询结果包含重复的行。 - Dmitry Bychenko

“ALL”意味着（ANSI SQL中）“保留重复项”。 - Dmitry Bychenko

Answer 1

SQL的EXCEPT运算符获取一个查询的不同行，并返回未出现在第二个结果集中的行。 EXCEPT ALL运算符不会删除重复项。 对于行消除和重复项删除的目的，EXCEPT运算符不区分NULL。

EXCEPT ALL从第一个表返回所有未在第二个表中出现的记录，保留重复记录。 不幸的是，SQL Server不支持此运算符。

- Pradeep Kumar Das

---

在 rextester.com 上尝试了一些查询。我发现 MySQL、Oracle、MS SQL Server 或 postgres 都不支持 EXCEPT ALL。它存在于哪个数据库引擎中？（只有 SQL Server 支持 EXCEPT） - MsA

Answer 2

except操作符返回第一个表格与第二个表格没有交集的部分。

令A=(10,11,12,10,10)

令B=(10,10)

A except B --> (11,12)

A except all B --> (10,11,12)

except从集合A中删除所有重复数据的出现，而except all只删除集合A中每个在集合B中出现的重复数据的一个出现。

- Fernando Rodriguez

---

它应该是（11、12、10）吗？还是说EXCEPT ALL删除了行的顺序与（10、10）相同的地方？ - joshpetit

Answer 3

最近我实现了INTERSECT ALL和EXCEPT ALL，但在SO上找不到太多相关资源。

---

考虑以下数据的例子。

你可以在sqlfiddle.com上重现这个例子，使用postgres 9.3。
请注意，大多数流行的数据库不支持INTERSECT ALL和EXCEPT ALL。当然可以使用row_number() over ()来绕过此问题。

create table x (V1 numeric);
create table y (V1 numeric);
insert into x values (1),(2),(2),(2),(3),(4),(4);
insert into y values (2),(3),(4),(4),(4),(5);

< p > EXCEPT [ALL] 匹配两个表中所有列，列的类型和顺序必须相同。

select * from x except select * from y;
| v1
----
|  1

select * from x except all select * from y;
| v1
----
| 1
| 2
| 2

在EXCEPT sql处理不同的数据集时，任何重复项都会自动删除，只留下每行的一份副本。这导致基于第二个数据集中的一行匹配而排除一行。
另一方面，EXCEPT ALL处理数据集时考虑了重复行的数量。这导致返回表之间重复行的确切差异。准确地说，是max(0, x.N - y.N)。
另一个棘手的运算符，与EXCEPT ALL非常相似，是INTERSECT ALL，它返回每个匹配行的min(x.N, y.N)个副本。
由于我提交的项目特性是开源的，我很高兴在此放置链接：github.com/Rdatatable/data.table。如果您想寻找用于基准测试的工具，可能会有用。 data.table是内存中且大多数为C实现的数据处理。它已经开源约10年了。

- jangorecki

---

在 rextester.com 上尝试了一些查询。我发现 MySQL、Oracle、MS SQL Server 或 postgres 都不支持 EXCEPT ALL。它存在于哪个数据库引擎中？（只有 SQL Server 支持 EXCEPT） - MsA

@anir 根据文档，除了所有内容都已经在Postgres中超过10年了。 - jangorecki

解释加上可重现的数据真是太棒了。我很惊讶你没有收到更多的赞。 - BenKoshy