Oracle减号和联合运算顺序/优先级 [英] Oracle minus and union order of operations/precedence
问题描述
我正在协助:
请考虑以下尝试查找一组中找到的所有记录，而不是另一组中找到的所有记录. (请注意，下面的--x表示结果集中预期的记录)
Consider the following trying to find all records found in one set but not the other. (note the --x below denotes records expected in result set)
Oracle数据库11g企业版11.2.0.4.0-64位
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
With test1 (A,b,c) as (
SELECT 2001, 'abc', 'john' from dual union all--
SELECT 2008, 'cab', 'sam' from dual union all--x
SELECT 2002, 'qwe', 'mike' from dual union all--x
SELECT 2002, 'asd', 'samuel' from dual union all--
SELECT 2012, 'ddd', 'sammy' from dual),--x
test2 (a,b,c) as (
SELECT 2001, 'abc', 'john' from dual union all--
SELECT 2008, 'c@b', 'saam' from dual union all--x
SELECT 2009, 'qwe', 'mike' from dual union all--x
SELECT 2002, 'asd', 'samuel' from dual union all--
SELECT 2001, 'a bc', 'john' from dual ),--x
cte as (Select * from test1 minus Select * from test2),
cte2 as (Select * from test2 minus Select * from test1)
Select * from cte
union
Select * from cte2;
这将产生预期的结果:
+------+------+-------+
| A | B | C |
+------+------+-------+
| 2001 | a bc | john |
| 2002 | qwe | mike |
| 2008 | c@b | saam |
| 2008 | cab | sam |
| 2009 | qwe | mike |
| 2012 | ddd | sammy |
+------+------+-------+
VS ...(为什么我们需要使用CTE?难道我们不可以只合并所有两个查询吗?)
VS... (why do we need to use a CTE? can't we just union all the two queries?)
With test1 (A,b,c) as (
SELECT 2001, 'abc', 'john' from dual union all
SELECT 2008, 'cab', 'sam' from dual union all
SELECT 2002, 'qwe', 'mike' from dual union all
SELECT 2002, 'asd', 'samuel' from dual union all
SELECT 2012, 'ddd', 'sammy' from dual),
test2 (a,b,c) as (
SELECT 2001, 'abc', 'john' from dual union all
SELECT 2008, 'c@b', 'saam' from dual union all
SELECT 2009, 'qwe', 'mike' from dual union all
SELECT 2002, 'asd', 'samuel' from dual union all
SELECT 2001, 'a bc', 'john' from dual )
Select * from test1 minus select * from test2
union ALL
Select * from test2 minus select * from test1
哪一个给我们.
+------+------+------+
| A | B | C |
+------+------+------+
| 2001 | a bc | john |
| 2008 | c@b | saam |
| 2009 | qwe | mike |
+------+------+------+
显然不是...为什么? (注释表明我是否将每个选择都包装在()中，以使其有效(并且确实)必须与操作中的优先级相等.)所以这可行:
Apparently not... Why? (comments indicate if I wrap each select in () for the minus it works (and it does) having to do with equal precedence in operation.) So this works:
(Select * from test1 minus select * from test2)
union ALL
(Select * from test2 minus select * from test1)
我知道我可以在相异之后对两个集合进行并集，然后进行一次有记数...但是为什么反负集合的并集不起作用?它是一个错误(或我无法在其上找到文档的功能吗?:P)[答案中有答案！]等待答案被接受！
I know I could union both sets after distinct and then do a having count... but why did the union of the inverse minused sets not work? Is it a bug (or a feature that I can't find doc on? :P) [Answered in comments!] waiting for an answer to accept!
SELECT *
FROM (SELECT Distinct * FROM test1 UNION ALL
SELECT Distinct * FROM test2)
GROUP BY A,B,C
HAVING count(*) = 1
所以...我的查询正在做的是:
So... what my query was doing was:
[评论中的答案，等待有人发帖！]
(Select * from test1 minus select * from test2 UNION ALL SELECT * FROM TEST2)
MINUS select * from test1
代替
(Select * from test1 minus select * from test2)
UNION ALL
(SELECT * FROM TEST2 MINUS select * from test1)
推荐答案
Select * from cte
union
Select * from cte2
此处在union中生成的cte分别评估2个表上的minus操作.
The cte s being unioned here evaluate minus operation on the 2 tables individually.
但是，此查询
Select * from test1 minus select * from test2
union ALL
Select * from test2 minus select * from test1
具有minus和union是设置运算符.所有集合运算符都具有相同的优先级.因此，他们从left到right进行了一对一评估.
has minus and union which are set operators. All set operators have equal precedence. Hence they get evaluated one-by-one from left to right.
因此，这些查询的结果是不同的.要明确指定顺序，请在两个查询之间使用括号.
Hence the results of these queries are different. To explicitly specify the order, use parentheses around the two queries.
(Select * from test1 minus select * from test2)
union
(Select * from test2 minus select * from test1)
这篇关于Oracle减号和联合运算顺序/优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
