6 Replies Latest reply: Feb 1, 2012 8:34 PM by rp0428

# Change a MINUS set operation to JOINS?

Hi guys,

I have a question about an SQL statement that I have to do. The complexity is big (lots of tables), but the problem can be resumed to this. Imagine I have the following query:
``````SELECT t.x
FROM the_table t
WHERE t.y = 'a'
MINUS
SELECT t.x
FROM the_table t
WHERE t.y = 'b'``````
In my original statement, I don't have only one table but many. But the issue is the same. I select a set of data and I minus the same statement with another criteria. I tried with some OR combination but never had the same result...

Does anyone have an idea?

Thanks,
• ###### 1. Re: Change a MINUS set operation to JOINS?
Hi,

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.

Best regards,
Nikolay
• ###### 2. Re: Change a MINUS set operation to JOINS?
If (x,y) is unique, you can use an outer join.
``````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``````
Edited by: 884476 on Feb 1, 2012 12:23 AM
• ###### 3. Re: Change a MINUS set operation to JOINS?
Hi,
In any case, you need to access the table twice, try the following:
``````select x
from the_table t
where y='a'
and x not in (select x
from the_table
where y='b')``````
But I have to ask something, why do you want to get rid of the MINUS?
• ###### 4. Re: Change a MINUS set operation to JOINS?
I like "analysis function" :-)

My "analysis function" article of OTN-Japan
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-1-323602-ja.html
``````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``````
• ###### 5. Re: Change a MINUS set operation to JOINS?
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``````
• ###### 6. Re: Change a MINUS set operation to JOINS?
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.