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

    Change a MINUS set operation to JOINS?

    user13117585
      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?
          Nikolay Savvinov
          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?
            887479
            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?
              Liron Amitzi
              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?
                Aketi Jyuuzou
                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?
                  Aketi Jyuuzou
                  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?
                    rp0428
                    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.