Sql query to return differences between two tables

Analyzing differences using SQL techniques

SQL is a language of many faces. Let's explore more methods to find differences.

Using EXCEPT to fish for unique rows

-- EXCEPT, not just a word in your breakup texts
-- Find rows unique to TableA
SELECT * FROM TableA
EXCEPT
SELECT * FROM TableB;


Column order and data types should match. Or else, you'll meet SQL's angry face.

Using EXISTS or its evil twin NOT EXISTS

-- NOT EXISTS - because everyone loves a good plot twist!
-- Find rows in TableA that decided not to show up in TableB
SELECT * FROM TableA a
WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.Key = a.Key);


NULL Handling: Those darn NULLs

Remember, NULLs are like your ex. They don't equate to anything, not even each other.

FULL JOIN: the icing on the cake

-- FULL JOIN - Because no one wants to leave the party early!
SELECT *
FROM TableA
FULL OUTER JOIN TableB ON TableA.Key = TableB.Key
WHERE TableA.Key IS NULL OR TableB.Key IS NULL;


It fetches those party-pooper rows that don't match up in either table.

Extra toppings on table comparison

Third-party tools: ApexSQL, because why not?

If SQL seems like hard work or your datasets are more twisted than an episode of "Sherlock", consider third-party tools like ApexSQL Data Diff. It offers an advanced GUI that makes life a lot easier.

Column comparison: Get specific

In case your tables have different columns or column order, column-wise comparison will save the day:

-- Comparing specific columns, cause we like to keep it simple!
SELECT a.FirstName AS A_FirstName, a.LastName AS A_LastName
FROM TableA a
FULL OUTER JOIN TableB b
ON a.FirstName = b.FirstName AND a.LastName = b.LastName
WHERE a.FirstName IS NULL OR b.FirstName IS NULL


Source it up with indicator columns

Sometimes, nailing down the source table is helpful:

-- Always know where your data comes from. It's only polite.
SELECT 'TableA' AS Source, A.*
FROM TableA A
WHERE NOT EXISTS (SELECT 1 FROM TableB B WHERE B.Key = A.Key)
UNION ALL
SELECT 'TableB', B.*
FROM TableB B
WHERE NOT EXISTS (SELECT 1 FROM TableA A WHERE A.Key = B.Key)


Data reconciliation: Because integrity matters

Use table comparison to reconcile data and ensure data integrity. It's crucial when you're pulling off fancy maneuvers like data migration or warehousing.