Sql – How to ‘subtract’ sql tables


Its not really a subtraction I'm looking for. And I know its not a union or intersection… I have been given a long and complex stored procedure that returns a table of active and inactive documents. I have also been given a similar stored procedure that returns another table that contains only the active documents.

How could I get a table of inactive documents using these two store procedures?

We are using SQL Server 2005.

Best Solution

The set operation you are looking for is called MINUS, but in SQL Server the keyword is EXCEPT

  SELECT ... // all documents
  SELECT ... // active documents

I believe that the EXCEPT set operation became available in SQL Server 2005.