In this example we find data that exists in one set but not the other. but why did I have to use CTE? (--x denote records expected in results)
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;
which results in expected results:
+------+------+-------+
| A | B | C |
+------+------+-------+
| 2001 | a bc | john |
| 2002 | qwe | mike |
| 2008 | c@b | saam |
| 2008 | cab | sam |
| 2009 | qwe | mike |
| 2012 | ddd | sammy |
+------+------+-------+
Select * from test1 minus select * from test2
union ALL
Select * from test2 minus select * from test1
Gives us
+------+------+------+
| A | B | C |
+------+------+------+
| 2001 | a bc | john |
| 2008 | c@b | saam |
| 2009 | qwe | mike |
+------+------+------+
I know if I wrap each select statement in ()'s it gives the 6 records expected... But why didn't the method without ()'s work?
Yes I know we could use an outer join and look for nulls, or union both sets and do a having count(*)=1 I'm just curious why this didn't work the way I expected!
It's to do with the order of precedence for set operators. In Oracle Database they all have the same priority.
So when you have:
Select * from test1 minus select * from test2
union ALL
Select * from test2 minus select * from test1
The processing is:
- Subtract from test1 all the rows in test2 (first minus)
- Add back in the rows from test2 (union all)
- Subtract all the rows from test1 (second minus)
You may notice that the first two steps cancel each other out ;)
So you can simplify this to just:
Select * from test2 minus select * from test1
Parentheses force the database to evaluate the expressions inside them first. Then evaluate those outside.
So:
( Select * from test1 minus select * from test2 )
union ALL
( Select * from test2 minus select * from test1 )
- Subtracts from test1 all the rows in test2 (first minus)
- Subtracts from test2 all the rows in test1 (second minus)
- Combines the results of these two operations (union all)
http://docs.oracle.com/database/122/SQLRF/About-SQL-Operators.htm#SQLRF51153