Does SQL Server Support the "MINUS" Keyword?

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:

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

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

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

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

|||

SQL Server has never supported MINUS.

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

|||

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.

|||

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