How do I 'subtract' sql tables?

Question

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.

Answer 1

select * from MyTable1
where MyTable1.Field1 not in (
  select Field1 from MyTable2)

Answer 2

Assuming there are unique IDs that correspond across the two tables:

select * from table_both b
where not exists (select * from table_active a where a.id = b.id)

Answer 3

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

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

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

Answer 4

I believe EXCEPT is what you are looking for. Syntax is similar to UNION or INTERSECT.

Answer 5

What's your DB engine?

In Oracle, you could use MINUS set operation.

In MS SQLServer 2005 and newer you can use EXCEPT.

Answer 6

In MS TSql, I think you want the EXCEPT keyword.

query1 EXCEPT query2

Which will return all rows found in the first query that are not also found in the second query.

Answer 7

SELECT * FROM Table1 
LEFT JOIN Table2 on Table1.id = Table2.id
WHERE Table2.id IS NULL

this should work on almost any database engine

Answer 8

SELECT both.*
FROM both LEFT OTUER JOIN inactives USING (whatever_id)
WHERE inactives.whatever_id IS NULL;

or

SELECT * FROM both
EXCEPT
SELECT * FROM inactives;

Answer 9

All good answers, but missing one point: The questioner (OP) has stored procedures...

You have to define temporary tables (based on your platform) to load the data

INSERT ...
EXEC getActive

INSERT ...
EXEC getInactive

Then use EXCEPT/EXISTS/MINUS/IN/OUTER JOIN/etc...