T-SQL: Comparing Two Tables - Records that don't exist in second table

Question

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.

I'm using Microsoft SQL Server 2005.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 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)

Answer 6

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/