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.

Answer 1

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 2

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