sql - Oracle 减号和联合运算顺序/优先级

Question

我正在协助:

How to find unmatched rows in oracle without using set operator and join & also Query the unmatched column names for an particular row

考虑以下尝试查找在一组中找到的所有记录而不是另一组中的所有记录。 (注意下面的 --x 表示结果集中预期的记录)

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？我们不能联合所有两个查询吗？)

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 |
+------+------+------+

显然不是……为什么？ (评论表明我是否将每个选择都包装在 () 中，因为它的工作(并且确实)必须在操作中具有相同的优先级。)所以这是有效的:

(Select * from test1 minus select * from test2)
union ALL
(Select * from test2 minus select * from test1)

我知道我可以在 distinct 之后合并两个集合，然后进行计数……但是为什么反向减去集合的并集不起作用？这是一个错误(还是我找不到文档的功能？:P)[在评论中回答!] 等待接受答案!

SELECT * 
FROM (SELECT Distinct * FROM test1 UNION ALL
      SELECT Distinct * FROM test2)
GROUP BY A,B,C
HAVING count(*) = 1

所以......我的查询正在做的是:

[从评论中回答等待某人发布!]

(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)

Answer 1

Select * from cte
union 
Select * from cte2

cte正在 union ed here 评价 minus分别对 2 个表进行操作。

然而，这个查询

Select * from test1 minus select * from test2
union ALL
Select * from test2 minus select * from test1

有 minus和 union它们是集合运算符。所有集合运算符具有相同的优先级。因此，它们从 left 中被一一评估。至 right .

因此这些查询的结果是不同的。要明确指定顺序，请在两个查询周围使用括号。

(Select * from test1 minus select * from test2)
union
(Select * from test2 minus select * from test1)

Documentation on set operators