T-SQL: Comparing Two Tables - Records that don't exist in second table
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))GOINSERT #PEOPLE VALUES (1, 'Bob')INSERT #PEOPLE VALUES (2, 'Steve')INSERT #PEOPLE VALUES (3, 'Jim')INSERT #EMPLOYEE VALUES (1, 'Bob')GOSELECT ID, NameFROM #PEOPLEEXCEPT SELECT ID, NameFROM #EMPLOYEEGOThe final query will return the two rows in the PEOPLE table which do not exist in the EMPLOYEE table.
Instead of using UNION, use EXCEPT, ( or INTERSECT to get only records in both )as described in
SELECT P.*FROM People PLEFT OUTER JOIN Employees E ON E.ID = P.ID -- Or whatever your PK-FK relationship isWHERE E.ID IS NULLFor SQL Server this will probably be the most performant way that you can do it.