CODEWITHSUNDEEP
sqloracle-database
Cornelius
Cornelius

Reputation: 15

Select both sides of Minus operator

I want to find the difference in table names of each owner for both sides of the minus query.

Say I have 2 select statements from a table and I want to compare them as such:

Select1: 

SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha';

Select2: 

SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie';

Say alpha's table has table names: a, b, c and charlie's table has table names b,c.

If I do

(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha') minus    
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie';

This gives me the expected result: a.

Conversely, if I do the opposite: 

  (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie') minus    
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha';

This gives me the expected result: empty row.

Now If I want both the table name 'a' and the empty row I perform: 

(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha') minus 
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie'
  Union
(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie') minus 
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha';

then I get nothing.

How would I return the 'a' row and the empty row combined? I am using Oracle DB

Upvotes: 0

Views: 507

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

It seems like what you want is UNION MINUS INTERSECT, if you get what I mean? Compute the union, compute the intersect, and do a MINUS between those two result sets (using appropriate parentheses, which is where your current efforts are failing because UNION and MINUS have the same precedence)

(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha' UNION 
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie')
MINUS
(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie' INTERSECT
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha');

This, I believe, correctly reflects what you're logically asking for. Zaynul Abadin Tuhin's answer addresses the immediate issue. But I believe your effort (and their fix) obscures the intent here.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

just use parenthesis after union

(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha') minus 
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie'

  Union
(
(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'charlie') minus 
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'alpha'
)

example

with t1 as
(
select 62054 as  sample_id,'2018-09-25 10:18:15' as timestamp,2652 as p_id from dual
union all
select 62054 as  sample_id,'2018-09-27 16:44:57' as timestamp,966 as p_id from dual
union all
select 62046 as  sample_id,null ,1809 as p_id from dual
union all
select 62046 , '018-09-25 10:18:15' ,2097  from dual



) select * from t1 where p_id=2652
 minus
 select * from t1 where p_id=966
 union

 select * from t1 where p_id=2652
 minus
 select * from t1 where p_id=2652

the above will return null but 

with t1 as
(
select 62054 as  sample_id,'2018-09-25 10:18:15' as timestamp,2652 as p_id from dual
union all
select 62054 as  sample_id,'2018-09-27 16:44:57' as timestamp,966 as p_id from dual
union all
select 62046 as  sample_id,null ,1809 as p_id from dual
union all
select 62046 , '018-09-25 10:18:15' ,2097  from dual



) select * from t1 where p_id=2652
 minus
 select * from t1 where p_id=966
 union
(
 select * from t1 where p_id=2652
 minus
 select * from t1 where p_id=2652
 )

the above will return below

SAMPLE_ID   TIMESTAMP   P_ID
62054   2018-09-25 10:18:15 2652

Upvotes: 2

Related Questions