SQL oracle中的减号运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20209265/
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
MINUS Operator in oracle
提问by Lizzie
MINUS Operator
减号运算符
I have 2 tables A and B.
我有 2 个表 A 和 B。
SELECT COUNT(*) FROM (SELECT * FROM tableA)
returns 389
返回 389
SELECT COUNT(*) FROM (SELECT * FROM tableB)
returns 217
返回 217
SELECT COUNT(*) FROM
(SELECT * FROM tableA
INTERSECT
SELECT * FROM tableB)
returns 0
返回 0
SELECT COUNT(*) FROM
(SELECT * FROM tableA
MINUS
SELECT * FROM tableB)
returns 389
返回 389
SELECT COUNT(*) FROM
(SELECT * FROM tableB
MINUS
SELECT * FROM tableA)
retuns 89
返回 89
Can someone please explain why does the last query return 89 and not 217?
有人可以解释为什么最后一个查询返回 89 而不是 217 吗？
回答by Jeffrey Kemp
MINUS takes the first result set, and removes any that exist in the second result set; it also removes any duplicates.
MINUS 取第一个结果集，并删除第二个结果集中存在的任何结果；它还会删除任何重复项。
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).
在您的示例中，tableA 有 389 行，tableB 有 217 行；您的 INTERSECT 显示没有共同的行，这意味着 tableA MINUS tableB 是 389 行（即所有行）。
tableB MINUS tableA returns the distinct set of rows in tableB, therefore there are 89 distinct values in tableB.
tableB MINUS tableA 返回 tableB 中不同的行集，因此 tableB 中有 89 个不同的值。
回答by akzhere
Suppose if you have set A and B, A = {1,2,3,4} and count(A) = 4, B = {5,6,6,7,7} and count(B) = 5
假设你已经设置了 A 和 B，A = {1,2,3,4} and count(A) = 4, B = {5,6,6,7,7} and count(B) = 5
A-B = {1,2,3,4} thus count(A-B) = count(A) = 4
AB = {1,2,3,4} 因此 count(AB) = count(A) = 4
But B-A = {5,6,7} and count(B) = 3
但是 BA = {5,6,7} 和 count(B) = 3
Thus what we understand here is that minus eliminates the duplicate terms(or rows). That's the reason why the row count reduced from 217 to 89.
因此，我们在这里理解的是减号消除了重复项（或行）。这就是行数从 217 减少到 89 的原因。
Hope this helps.
希望这可以帮助。
