首页 > IT知识库 > 数据库 >

PostgreSQL如何实现集合运算符

关键是当使用全部时,“结果集”不再表现为“集合”,而是表现为“列表”。

ANSI SQL标准定义了集合运算符UNION,EXCEPT和INTERSECT。
每个都有2个变体-DISTINCT和ALL。
另外,每个都可以是SET或MULTISET类型。
据我所知:

只有PostgreSQL支持EXCEPT和INTERSECT的ALL变体。
仅Oracle支持MULTISET。
更新-Schwern在一条评论中通知我,MariaDB还支持集合运算符ALL

对于这个问题,我将使用EXCEPT示例,尽管INTERSECT也是如此

ANSI SQL(ISO / IEC CD 9075-2)标准定义了以下内容:

Page 454第7.16节“查询表达式”-第16条:如果在“查询项”或“查询表达式主体”中指定了集合运算符,则:a)令T1,T2和TR分别为第一个操作数,即第二个操作数,以及“查询词”或“查询表达式主体”的结果。 b)假设TN1和TN2分别是T1和T2的有效名称。 c)如果集合运算符是UNION DISTINCT,EXCEPT ALL,EXCEPT DISTINCT,INTERSECT ALL或INTERSECT DISTINCT,则T1和T2的每一列都是分组操作的操作数...

Page 54第4.10节集合类型-项目6.2:MULTISET EXCEPT是一个运算符,用于计算两个多集的多集差。有两种变体,ALL和DISTINCT。由ALL指定的变量在结果中放置一个值的实例数,该值等于第一个操作数中该值的实例数减去第二个操作数中该值的实例数。 DISTINCT指定的变体从结果中删除重复项。

考虑到这一点,请考虑以下脚本(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的结果不是我期望的。它返回3、3、2、2、1的5行。

PostgreSQL文档确认了此行为,但是基于ANSI定义,我希望仅返回2、2、1的3行。

第一个引号表明DISTINCT将分组操作应用于限定该操作的行。此示例3中的恕我直言不应符合EXCEPT的条件,因此根本不应返回。

减去(m-n)个元素的行为似乎与上面第二个引号中定义的MULTISET操作一致。但是,PostgreSQL文档指出它甚至不支持基本的多集,当然那些要求我们明确指定MULTISET EXCEPT ALL。

我在这里想念什么?
根据我收到的评论和答复,我想澄清一下,我理解这里的EXCEPT ALL。 PostgreSQL文档对此很明确。我的问题是这是否是(SET)EXCEPT ALL的正确行为。我的理解是,这就是MULTISET EXCEPT ALL的目的。

尽管我同意令人困惑的措辞/关键字,但EXCEPT ALL并不会删除重复项。 因此,它仅从第一个查询中删除一个3,而其余两个3则保持不变。 如果在第二个查询中有更多的3,它将删除更多的行。 关键是当使用全部时,“结果集”不再表现为“集合”,而是表现为“列表”。但是,您对标准的第二次引用是这样的:“由ALL指定的变量在结果中放置一个值的实例数,该值等于第一个操作数中该值的实例数减去该值的实例数 在第二个操作数中”。 因此,指出它们应该作为列表工作。