Sql query to return differences between two tables

sql

data-integrity

sql-techniques

data-comparison

byAlex Kataev·Sep 4, 2024

If fast and furious is how you like your coding, FULL OUTER JOIN is your beast. Here's a quick snippet to find those elusive differences between two tables:

-- Works faster than Speedy Gonzales!
SELECT COALESCE(TableA.Key, TableB.Key) AS Key, TableA.*, TableB.*
FROM TableA
FULL OUTER JOIN TableB ON TableA.Key = TableB.Key
WHERE TableA.Key IS NULL OR TableB.Key IS NULL;

COALESCE here ensures we have a non-null key, while * bring all columns to the party. So sit tight, folks. This will fetch all the rows that aren't playing matchy-matchy in both 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.

explain-codes / Sql / Sql query to return differences between two tables

Linked

Check if entry in table A exists in table B



Select a value where it doesn't exist in another table



Find records from one table which don't exist in another



Delete SQL rows where IDs do not have a match from another table



Select statement to find duplicates on certain fields

