Posted By: Anonymous
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.
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.
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.
IF you have tables
B, both with colum
C, here are the records, which are present in table
A but not in
SELECT A.* FROM A LEFT JOIN B ON (A.C = B.C) WHERE B.C IS NULL
To get all the differences with a single query, a full join must be used, like this:
SELECT A.*, B.* FROM A FULL JOIN B ON (A.C = B.C) WHERE A.C IS NULL OR B.C IS NULL
What you need to know in this case is, that when a record can be found in
A, but not in
B, than the columns which come from
B will be NULL, and similarly for those, which are present in
B and not in
A, the columns from
A will be null.