none
Does SQL Server Support the "MINUS" Keyword?

    Question

  • Maybe there's another better way to do this...  Anyway, here's what I'm trying to do:

    I have two tables CarType & Cars.  Table CarType has a column CarTypeId which is the primary key (int, identity).  Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.

    Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).

    I read that there is an SQL "MINUS" keyword that you can use like this:

    SELECT CarTypeId FROM CarType
    MINUS
    SELECT CarTypeId FROM Cars

    So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}.  (Note:  I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).

    I tried it in SQL Server 2005 Express.  The result is just {1, 2, 3, 4, 5}.

    My questions:

    1. Is the MINUS capability supported by SQL Server?  I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server...
    2. If not supported, well then I guess I have to throw myself at the feet of the real SQL gurus and ask if there's another way to accomplish this.

    Here's a reference to the website where I initially found out about MINUS:
    http://www.1keydata.com/sql/sql-minus.html

    Thanks in advance for your time/comments.

    • Moved by Tom PhillipsModerator Tuesday, January 26, 2010 1:53 PM TSQL Question (From:SQL Server Database Engine)
    Wednesday, March 22, 2006 11:33 PM

Answers

  • I think you want EXCEPT

    ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

     

    Wednesday, March 22, 2006 11:55 PM
  • SQL Server has never supported MINUS.

    in SQL 2005 it now has the EXCEPT clause which does the same thing

    Thursday, March 23, 2006 7:38 AM
  • Thanks Simon for your comments and pointing me in the right direction.

    This is what worked for me:

    SELECT CarTypeId FROM CarType
    WHERE (CarTypeId NOT IN
                         (SELECT DISTINCT CarTypeId FROM Cars)
                   
    )

    Still interested to know what became of the MINUS keyword though, because I think that syntax is slicker...

    Ian

    Thursday, March 23, 2006 4:26 AM

All replies

  • I think you want EXCEPT

    ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

     

    Wednesday, March 22, 2006 11:55 PM
  • You can also use a NOT EXISTS to find records that don't exist in the other table
    Wednesday, March 22, 2006 11:56 PM
  • Thanks Simon for your comments and pointing me in the right direction.

    This is what worked for me:

    SELECT CarTypeId FROM CarType
    WHERE (CarTypeId NOT IN
                         (SELECT DISTINCT CarTypeId FROM Cars)
                   
    )

    Still interested to know what became of the MINUS keyword though, because I think that syntax is slicker...

    Ian

    Thursday, March 23, 2006 4:26 AM
  • SQL Server has never supported MINUS.

    in SQL 2005 it now has the EXCEPT clause which does the same thing

    Thursday, March 23, 2006 7:38 AM
  •  

    Caution: Microsoft talks about this issue at https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0761.mspx?mfr=true.  However, it is incomplete in that it does not address a curveball.

     

    That is, the = operator does not address nulls. 

     

    The Microsoft example is as follow:

     

    Oracle Microsoft SQL Server

    SELECT CCODE, CNAME
    FROM DEPT_ADMIN.CLASS
    MINUS
    SELECT C.CCODE, C.CNAME
    FROM STUDENT_ADMIN.GRADE G,    
    DEPT_ADMIN.CLASS C
    WHERE C.CCODE = G.CCODE

    SELECT CCODE, CNAME
    FROM DEPT_ADMIN.CLASSC
    WHERE NOT EXISTS
    (SELECT 'X' FROM
    STUDENT_ADMIN.GRADE G
    WHERE C.CCODE = G.CCODE)

     

    This will work to identify students in one table but not in another.  However, it will not work like Oracle's MINUS command to spot differences between tables.  So, say you want to see this -- between the 2 tables, find where students are missing AND students whose names have changed -- either from A to B or from not null to null or from null to not null.

     

    Try this:

     

    SELECT CCODE, CNAME
    FROM DEPT_ADMIN.CLASS C
    WHERE NOT EXISTS
    (SELECT 'X' FROM
      STUDENT_ADMIN.GRADE G
      WHERE C.CCODE = G.CCODE

      and (C.CNAME = G.CNAME or (C.CNAME is null and G.NAME is null) )

     

    That will mimic the full functionality of Oracle's MINUS command in SQL Server 2000 T-SQL.

     

     

    Monday, September 03, 2007 12:20 PM
  • Hi Ian,

     

    I havent come across the Minus in SQL server but to do what you want I would try the following

     

    SELECT T.cartypeid

    FROM cartype as T

    LEFT OUTER JOIN cars C

    ON T.cartypeID = C.cartypeID

    WHERE c.caretypeID is null

     

    This joins everything in the cartype table but the where clause restricts the result set to those records with a null car.cartypeID

    i.e. no matching record

    Monday, September 03, 2007 8:51 PM
  •  

    SELECT DISTINCT  CarTypeId FROM CarType
    WHERE (CarTypeId NOT IN
                         (SELECT CarTypeId FROM Cars)
                   
    )

     

    the command DISTINCT  goes in the first query, Because but it is like that, the query show the repeated rows....

     

    sorry for my english because my language is Spanish , and even I do not do a course....  xD

     

    bye... Regards from chili....

     

    Max.

     

    Friday, October 19, 2007 11:41 PM
  •  

    hey johnAH,

     

    Thats a genius of a query. How did u manage to write that query. I was trying to simulate the 'MINUS' operation with other 'NOT IN', 'NOT EXISTS' keywords but they take huge amounts of time to return results. But the LEFT OUTER JOIN query of yours has improved the performance by huge magnitude. It used to take 122 seconds, now its taking barely 6 seconds to execute the query. Hats off man.

     

    Thank you.

    Chandra.
    Wednesday, March 26, 2008 5:01 PM
  • NOT queries tend to give query optimizer's fits, resulting in table scans instead of using any indices.  By converting the query to use a LEFT OUTER JOIN instead of a NOT EXISTS, the query optimizer can use any appropriate indices defined on the table(s).

     

    Carl.

     

    Thursday, May 22, 2008 5:43 PM
  • Carl,

     

    Thats just plain wrong. A very old myth.

     

    A LEFT OUTER JOIN will often result in a more complex plan because you are joining to possibly many rows, whereas the EXISTS only has to check for the existence of one record. If you have the appropriate indexes then both scenarios may use them (I say may because it depends on the rest of the query).

     

     

    Thursday, May 22, 2008 6:43 PM
  • I think you want EXCEPT

    ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

     

    Great, helped me. Thanks
    Wednesday, November 25, 2009 8:07 PM
  • JohnAH,

    My empirical data - using LEFT OUTER JOIN in conjunction with NULL in where clause - 13 seconds.

    Simon - the 13 seconds was not quick enough so checked out the EXCEPT syntax you provided a link to - cut the time down to 3 seconds.

    Thank you both very much,

    Amit Kohli
    Monday, January 25, 2010 9:59 PM
  • so  that  :

    SELECT CarTypeId FROM CarType
    except

    SELECT CarTypeId FROM Cars

    Friday, July 16, 2010 2:34 AM
  • And this one too 
     http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/ 

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, July 16, 2010 3:28 PM
  • Try This...

    Select distinct
    (
    (Select sum(P.s_Qty) from tb_StorePurchase P) 
     -
    (Select sum(T.n_Qty) from tb_StoreTaxInvoice T ) 
    )  AS 'Total'
    from tb_StorePurchase P ,tb_StoreTaxInvoice T 
    where P.s_style = @Ps_style and T.s_Stylcode = @Ps_Stylcode

    Use your values at @Ps_style and  @Ps_Stylcode 


    Friday, March 09, 2012 6:52 AM