How do I '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.

回答1:

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.

回答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)

回答3:

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

回答4:

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

this should work on almost any database engine

回答5:

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

回答6:

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

回答7:

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;

回答8:

What's your DB engine?

In Oracle, you could use MINUS set operation.

In MS SQLServer 2005 and newer you can use EXCEPT.

回答9:

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.

回答10:

You can also do this with the NOT IN clause

For example, assuming the stored procedures have given you table variables called @AllDocuments and @ActiveDocuments and each document has an identifier column called DocId

SELECT * FROM @AllDocuments 
WHERE DocId NOT IN 
    (SELECT DocId FROM @ActiveDocuments)

Adapt this as appropriate to match your table / column names.

回答11:

SELECT roll_number FROM profile WHERE(catagory='Attest and Eat' or catagory='Live and Eat') and status='OK' EXCEPT SELECT roll_number from meal_status  WHERE date='29' AND month='1'

You can try this kind of command to subtract a table from another one.

回答12:

For doing the subtraction between three tables I have used the following query:

Basically I have three tables.. table 1, table 2, table 3. Firstly I have done the subtraction of table 1 and table 2 and then done the subtraction between the the result of previous query and table 3.

select v3.Material, ((v1.Qty-v2.Qty)-v3.Qty) as Quantity
  from table1 v1, table2 v2, table3 v3
 where (v1.Material=v2.Material
    and v1.Material=v3.Material
    and v2.Material=v3.Material)

回答13:

You can just used the first sp that return the Active & Inactive and in WHERE cluse put condition for the document status =inactive, you wil get inactive document only.