Does SQL Server Support the "MINUS" Keyword?

_{Wednesday, March 22, 2006 11:33 PM | 2 votes}

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.

_{Wednesday, March 22, 2006 11:55 PM ✅Answered | 4 votes}

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

_{Thursday, March 23, 2006 7:38 AM ✅Answered | 1 vote}

SQL Server has never supported MINUS.

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

_{Wednesday, March 22, 2006 11:56 PM}

You can also use a NOT EXISTS to find records that don't exist in the other table

_{Thursday, March 23, 2006 4:26 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

_{Monday, September 3, 2007 12:20 PM | 1 vote}

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:

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 3, 2007 8:51 PM | 1 vote}

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

_{Friday, October 19, 2007 11:41 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.

_{Wednesday, March 26, 2008 5:01 PM | 1 vote}

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.

_{Thursday, May 22, 2008 5:43 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 6:43 PM | 1 vote}

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).

_{Wednesday, November 25, 2009 8:07 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

_{Monday, January 25, 2010 9:59 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

_{Friday, July 16, 2010 2:34 AM}

so  that  :

SELECT CarTypeId FROM CarType
except
SELECT CarTypeId FROM Cars

_{Friday, July 16, 2010 3:28 PM}

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, March 9, 2012 6:52 AM}

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 9, 2012 7:03 AM | 1 vote}

Related articles:

SQL SERVER – EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle

Except Operator in SQL Server 2005

http://www.sqlusa.com/bestpractices2008/compare-tables/

Kalman Toth SQL SERVER & BI TRAINING
**New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
**

_{Friday, November 20, 2015 8:49 AM}

Your query ...

SELECT CarTypeId FROM CarType
MINUS
SELECT CarTypeId FROM Cars

... does not only list {1, 2, 3, 4, 5}, but also the result of the second statement. The MINUS "operator" is nothing but a table alias, because it's not a language keyword. In fact, this is what the statements really are:

SELECT CarTypeId FROM CarType AS MINUS;
SELECT CarTypeId FROM Cars;