sql query to return differences between two tables

Question

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 1

Let's say you are working on tables A and B, and if both the tables have column C. 

Rows/Records, which are present in table A but not in B are as follows:

SELECT A.*

FROM A

LEFT JOIN B ON (A.C = B.C)

WHERE B.C IS NULL

The full join must be used in order to get all the differences with a single query 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

Note: When a record can be found in A, but not in B, then 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.

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in an industry-recognized SQL online course.

Answer 2

To compare the two tables and returning only those records present in either of the tables, you can use any one of the below approaches: 

1. Using “Union ALL” and “Except” clause - 

SELECT *

FROM (

    SELECT FirstName, LastName, Product

    FROM TableA

    EXCEPT

    SELECT FirstName, LastName, Product

    FROM TableB

    UNION ALL

    SELECT FirstName, LastName, Product

    FROM TableB

    EXCEPT

    SELECT FirstName, LastName, Product

    FROM TableA

) AS Discrepancies;

  SELECT FirstName, LastName, Product

    FROM TableA

    EXCEPT

    SELECT FirstName, LastName, Product

    FROM TableB

This part finds all the records present in Table A but not in B 

 SELECT FirstName, LastName, Product

    FROM TableB

    EXCEPT

    SELECT FirstName, LastName, Product

    FROM TableA

This part finds all records present in Table B but not in A. 

Union ALL combines the results of both the queries and give the desired output. 

2. Using Full Outer join 

SELECT A.FirstName AS A_FirstName, A.LastName AS A_LastName, A.Product AS A_Product,

       B.FirstName AS B_FirstName, B.LastName AS B_LastName, B.Product AS B_Product

FROM A

FULL JOIN B

ON A.FirstName = B.FirstName

   AND A.LastName = B.LastName

   AND A.Product = B.Product

WHERE A.FirstName IS NULL OR B.FirstName IS NULL

   OR A.LastName IS NULL OR B.LastName IS NULL

   OR A.Product IS NULL OR B.Product IS NULL;

Full join combines rows from A and B and the where clause filters the rows when there is a discrepancy, i.e. one table has the data but the other table does not.