This content has been marked as final. Show 6 replies
set operations (like UNION, UNION ALL, MINUS, INTERSECT) and JOINs have a key difference: set operations combine rows while joins combine columns. You can't replace one with another. Moreover, it's not clear why you would want that: what's wrong with using MINUS in your queries?
BTW in your example the minused part looks redundant (since t.y can't equal 'a' and 'b' at the same time) -- if you drop it, the meaning of the query won't change.
If (x,y) is unique, you can use an outer join.
Edited by: 884476 on Feb 1, 2012 12:23 AM
SQL> select * 2 from the_table; X Y ---------- ---------- AA a BB a CC a AA b DD b SQL> SELECT t.x 2 FROM the_table t 3 WHERE t.y = 'a' 4 MINUS 5 SELECT t.x 6 FROM the_table t 7 WHERE t.y = 'b'; X ---------- BB CC SQL> SELECT t.x 2 FROM (select * from the_table t1 where y = 'a') t 3 left outer join 4 (select * from the_table t3 where y = 'b') t2 5 on( t.x = t2.x) 6 where t2.x is null; X ---------- BB CC
In any case, you need to access the table twice, try the following:
But I have to ask something, why do you want to get rid of the MINUS?
select x from the_table t where y='a' and x not in (select x from the_table where y='b')
I like "analysis function" :-)
My "analysis function" article of OTN-Japan
with the_table(X,Y) as( select 'AA','a' from dual union select 'BB','a' from dual union select 'CC','a' from dual union select 'AA','b' from dual union select 'DD','b' from dual), tmp as( select distinct X, max(decode(Y,'a',1,0)) over(partition by X) as ExistA, max(decode(Y,'b',1,0)) over(partition by X) as ExistB from the_table) select * from tmp where ExistA = 1 and ExistB = 0; X ExistA ExistB -- ------ ------ BB 1 0 CC 1 0
OOPS there is more simple solution :8}
with the_table(X,Y) as( select 'AA','a' from dual union select 'BB','a' from dual union select 'CC','a' from dual union select 'AA','b' from dual union select 'DD','b' from dual) select X from the_table group by X having max(decode(Y,'a',1,0)) = 1 and max(decode(Y,'b',1,0)) = 0; X -- BB CC
Please post a realistic example that demonstrates your problem. As Nikolay stated your MINUS isn't even needed making what you posted useless in trying to understand what you need to do.
So for starters post the following:
1. What Oracle version you are using for each server.
2. How many servers are involved.
3. Is this a new process or a current process that has performance or other issues? If a current process how is it being done now? What are the issues?
4. A statement of exactly what you are trying to do; not how you think it ought to be done but what the goal is.
5. A realistic example with actual tables, columns and queries
6. How many columns are there in the result set?
A sample set of source data and the set of result data you want to produce from that source.