sql query to return differences between two tables

Casey picture Casey · Jan 16, 2010 · Viewed 651.3k times · Source

I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the discrepancies. I need to check three pieces of data in doing so, FirstName, LastName and Product.

I'm fairly new to SQL and it seems like a lot of the solutions I'm finding are over complicating things. I don't have to worry about NULLs.

I started by trying something like this:

SELECT DISTINCT [First Name], [Last Name], [Product Name] FROM [Temp Test Data]
WHERE ([First Name] NOT IN (SELECT [First Name] 
FROM [Real Data]))

I'm having trouble taking this further though.

Thanks!

EDIT:

Based on the answer by @treaschf I have been trying to use a variation of the following query:

SELECT td.[First Name], td.[Last Name], td.[Product Name]
FROM [Temp Test Data] td FULL OUTER JOIN [Data] AS d 
ON td.[First Name] = d.[First Name] AND td.[Last Name] = d.[Last Name] 
WHERE (d.[First Name] = NULL) AND (d.[Last Name] = NULL)

But I keep getting 0 results back, when I know that there is at least 1 row in td that is not in d.

EDIT:

Ok, I think I figured it out. At least in my few minutes of testing it seems to work good enough.

SELECT [First Name], [Last Name]
FROM [Temp Test Data] AS td
WHERE (NOT EXISTS
        (SELECT [First Name], [Last Name]
         FROM [Data] AS d
         WHERE ([First Name] = td.[First Name]) OR ([Last Name] = td.[Last Name])))

This is basically going to tell me what is in my test data that is not in my real data. Which is completely fine for what I need to do.

Answer

erikkallen picture erikkallen · Jan 16, 2010
(   SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2)  
UNION ALL
(   SELECT * FROM table2
    EXCEPT
    SELECT * FROM table1)