sql - 集合运算符的 PostgreSQL 实现

Question

大家好，提前感谢您的时间。

ANSI SQL 标准定义了集合运算符 UNION、EXCEPT 和
相交。

每个都有 2 个变体 - DISTINCT 和 ALL。

此外，每个都可以是 SET 或 MULTISET 类型。

AFAIK:

只有 PostgreSQL 支持 EXCEPT 和 INTERSECT 的 ALL 变体。

只有 Oracle 支持 MULTISET。

更新 - Schwern 在评论中告诉我，MariaDB 还支持集合运算符的 ALL
对于这个问题，我将使用 EXCEPT 示例，尽管 INTERSECT 也是如此。
ANSI SQL (ISO/IEC CD 9075-2) 标准定义了以下内容:

Page 454 Section 7.16 'query expression' - Item 16: If a set operator is specified in a 'query term' or a 'query expression body', then: a) Let T1, T2, and TR be respectively the first operand, the second operand, and the result of the 'query term' or 'query expression body'. b) Let TN1 and TN2 be the effective names for T1 and T2, respectively. c) If the set operator is UNION DISTINCT, EXCEPT ALL, EXCEPT DISTINCT, INTERSECT ALL, or INTERSECT DISTINCT, then each column of T1 and T2 is an operand of a grouping operation...

Page 54 Section 4.10 Collection Types - Item 6.2: MULTISET EXCEPT is an operator that computes the multiset difference of two multisets. There are two variants, ALL and DISTINCT. The variant specified by ALL places in the result a number of instances of a value, equal to the number of instances of the value in the first operand minus the number of instances of the value in the second operand. The variant specified by DISTINCT removes duplicates from the result.

考虑到这一点，请考虑以下脚本(PostgreSQL 12):

CREATE TABLE T1 (C1 INT);
CREATE TABLE T2 (C1 INT);

INSERT INTO T1 VALUES (1), (2), (2), (3), (3), (3);
INSERT INTO T2 VALUES (3);

SELECT  * FROM T1
EXCEPT DISTINCT
SELECT * FROM T2;

SELECT * FROM T1
EXCEPT ALL
SELECT * FROM T2;

DROP TABLE T1, T2;

EXCEPT DISTINCT(默认值)的结果与预期一致:2 行，分别为 1, 2。
EXCEPT ALL 的结果不是我所期望的。它返回 5 行，分别为 3、3、2、2、1。
PostgreSQL 文档证实了这种行为，但根据 ANSI 定义，我希望只返回 3 行，其中包含 2、2、1。
第一个引用表明 DISTINCT 将分组操作应用于符合该操作的行。恕我直言，在这个例子 3 中不应该限定 EXCEPT，因此根本不应该返回。
减去 (m-n) 元素的行为似乎与上面第二个引用中定义的 MULTISET 操作一致。然而，PostgreSQL 文档声明它甚至不支持基本的多重集，当然那些需要我们明确指定 多组除了所有
我在这里缺少什么？
根据我收到的评论和回复，我想澄清一下，我理解 EXCEPT ALL 在这里做什么。 PostgreSQL documentation对此很清楚。我的问题是这是否是 (SET) EXCEPT ALL 的正确行为。我的理解是，这就是 MULTISET EXCEPT ALL 的用途。
预先感谢您的想法。
保持安全和健康!

Answer 1

EXCEPT将为您提供不同的结果，消除您验证的表中存在的值。它类似于 MINUS关系代数中的运算符。
拿你的例子来说，

CREATE TABLE T1 (C1 INT);
CREATE TABLE T2 (C1 INT);

INSERT INTO T1 VALUES (1), (2), (2), (3), (3), (3);
INSERT INTO T2 VALUES (3);

EXCEPT将为您提供独特的值(value)，
EXCEPT所有人都会为您提供消除没有的结果。值的出现次数。
在 T2 表中，只有一行的值为 3，但在 T1 表中，有三行的值为 3。当您使用 EXCEPT ALL 时它只会忽略第二个表中出现的一个事件并为您提供结果。

现在我们可以尝试在值为 3 的 T2 表中插入三行并检查结果。

INSERT INTO T1 VALUES (1), (2), (2), (3), (3), (3);
INSERT INTO T2 VALUES (3), (3), (3);

您在 EXCEPT 的结果保持不变，因为我们只有值 3。
但是 EXCEPT ALL的结果在下面，它消除了 3 次出现的值为 3 的行