T-SQL: Comparing Two Tables - Records that don'

问题:

If UNION ALL is an addition in T-SQL. What is the equivalent of subtraction?

For example, if I have a table PEOPLE and a table EMPLOYEES. And I know if I remove EMPLOYEES records from PEOPLE I will be left with my companies CONTRACTORS.

Is there a way of doing this that is similar to UNION ALL? One where I don't have to specify any field names? The reason I ask is this is just one hypothetical example. I need to do this several times to many different tables. Assume that the schema of EMPLOYEES and PEOPLE are the same.

回答1:

Instead of using UNION, use EXCEPT, ( or INTERSECT to get only records in both ) as described in

msdn EXCEPT Link for Sql2k8

msdn EXCEPT Link for Sql2k5

回答2:

You can use the EXCEPT operator to subtract one set from another. Here's a sample of code using EMPLOYEES and PEOPLE temporary tables. You'll need to use the field names with the EXCEPT operator as far as I know.

CREATE TABLE #PEOPLE
(ID INTEGER,
 Name NVARCHAR(50))

CREATE TABLE #EMPLOYEE
(ID INTEGER,
 Name NVARCHAR(50))
GO

INSERT #PEOPLE VALUES (1, 'Bob')
INSERT #PEOPLE VALUES (2, 'Steve')
INSERT #PEOPLE VALUES (3, 'Jim')
INSERT #EMPLOYEE VALUES (1, 'Bob')
GO

SELECT ID, Name
FROM #PEOPLE
EXCEPT 
SELECT ID, Name
FROM #EMPLOYEE
GO

The final query will return the two rows in the PEOPLE table which do not exist in the EMPLOYEE table.

回答3:

SELECT
     P.*
FROM
     People P
LEFT OUTER JOIN Employees E ON
     E.ID = P.ID     -- Or whatever your PK-FK relationship is
WHERE
     E.ID IS NULL

For SQL Server this will probably be the most performant way that you can do it.

回答4:

SELECT * FROM Table1
WHERE Table1.Key NOT IN (SELECT Table2.Key FROM Table2 WHERE Table2.Key IS NOT NULL)

Added IS NOT NULL to make people happy.

I would agree with Tom. His version is most likely more efficient. The only possible reason to use mine, might be that it's prettier.

回答5:

Unfortunately there is a problem in your design. instead of having two table PEOPLE and CONTRACTOR. You should have a table PEOPLE and another Table TYPE (if some people can have several role another table maybe needed). In your PEOPLE table you make a referece to the TYPE table.

then you requests become

SELECT * from PEOPLE, TYPE
WHERE PEOPLE.type_id = TYPE.id 
AND TYPE.name = 'CONTRACTOR'

SELECT * from PEOPLE, TYPE
WHERE PEOPLE.type_id = TYPE.id 
AND TYPE.name = 'EMPLOYEE'

(untested)

回答6:

When I compare tables looking for data that isn't in one that is in the other I typically use SQL Division.

select *(or selected matching field) 
from tableA as A
where not exist
(select *(or selected matching field) 
from tableB as B 
where A.key = B.key)

This query will return the results that are in tableA that are not in through the process of division.

select *(or selected matching field) 
from tableA as A
where exist
(select *(or selected matching field) 
from tableB as B 
where A.key = B.key)

This query will return all the rows of data that match in both tables therefore if there is a row data that is in tableA that isn't in tableB that row of data will not be retrieved.

回答7:

I found it is a lot easier to use a tool like SQLMerger to do this for you. The results are displayed in a nicer way and you can go on with whatever you need to do with the data thereafter easily.

www.auisoft.com/SQLMerger <= the tool that makes it easy to compare data

example on comparing two tables: http://auisoft.com/SQLMerger/How-to/visualize-differences-in-2-databases/