oracle中的MINUS运算符 [英] MINUS Operator in oracle

问题描述

我有2个表A和B.

SELECT COUNT(*) FROM (SELECT * FROM tableA)

返回389

SELECT COUNT(*) FROM (SELECT * FROM tableB)

返回217

SELECT COUNT(*) FROM
(SELECT * FROM tableA
INTERSECT
SELECT * FROM tableB)

返回0

SELECT COUNT(*) FROM 
(SELECT * FROM tableA
MINUS
SELECT * FROM tableB)

返回389

SELECT COUNT(*) FROM 
(SELECT * FROM tableB
MINUS
SELECT * FROM tableA)

重弹89

有人可以解释一下为什么最后一个查询返回89而不是217吗?

Can someone please explain why does the last query return 89 and not 217?

推荐答案

MINUS提取第一个结果集，并删除第二个结果集中存在的任何结果； 它还会删除所有重复项.

MINUS takes the first result set, and removes any that exist in the second result set; it also removes any duplicates.

在您的示例中，表A有389行，表B有217行；您的INTERSECT显示没有共同的行，这意味着tableA MINUS tableB是389行(即所有行).

In your example, tableA has 389 rows, and tableB has 217 rows; your INTERSECT shows there are no rows in common, which means tableA MINUS tableB is 389 rows (i.e. all of them).

tableB MINUS tableA返回tableB中不同的行集，因此tableB中有89个不同的值.

tableB MINUS tableA returns the distinct set of rows in tableB, therefore there are 89 distinct values in tableB.

这篇关于oracle中的MINUS运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！