Compare two tables, find missing rows and mismatched data

Question

I'd like to compare two tables and get a set of results where the lookup values are mismatched as well as where the key values are missing from the other table. The first part works fine with the following query:

SELECT * FROM (
    SELECT  mID, mLookup
    FROM m) t1

FULL OUTER JOIN (
    SELECT  aID, aLookup
    FROM a) t2

ON t1.mID = t2.aID

WHERE
    t1.mID = t2.aID AND
    t1.mLookup <> t2.aLookup

However, it doesn't return rows from t1 and t2 where there is no corresponding ID in the other table (because of the ON t1.mID = t2.aID).

How can I achieve both in the same query?

What database system you're using? – Aleksandr Fedorenko Apr 10 '13 at 13:44 — Aleksandr Fedorenko, Apr 10 '13 at 13:44

Paul Williams · Accepted Answer · 2013-04-10T13:35:35.987

Remove the ID part of the WHERE clause. The FULL OUTER JOIN ON t1.mID = t2.aID is enough to link the tables together. The FULL OUTER JOIN will return both tables in the join even if one does not have a match.

However, the WHERE t1.m_ID = t2.aID clause limits the results to IDs that exist in both tables. This effectively causes the FULL OUTER JOIN to act like an INNER JOIN.

In other words:

SELECT * FROM (
    SELECT  mID, mLookup
    FROM m) t1

FULL OUTER JOIN (
    SELECT  aID, aLookup
    FROM a) t2

ON t1.mID = t2.aID

WHERE
    --t1.mID = t2.aID AND -- remove this line
    t1.mLookup <> t2.aLookup

-- EDIT --

Re-reading your question, you wanted only the mismatches. In that case, you need to search on where either side's ID is NULL:

SELECT * FROM (
    SELECT  mID, mLookup
    FROM m) t1

FULL OUTER JOIN (
    SELECT  aID, aLookup
    FROM a) t2

ON t1.mID = t2.aID

WHERE
    t1.mID IS NULL OR
    t2.mID IS NULL OR
    t1.mLookup <> t2.aLookup

score 3 · Answer 2 · answered Apr 10 '13 at 13:30

The where clause of your query filters out those rows that dont have matching "Ids". Try this:

SELECT m.mId, m.mLookup, a.aId, a.aLookup
 from m
  full outer join a
   on a.aId = m.mId
 where m.mId is null
  or a.aID is null
  or m.mLookup <> a.aLookup

The full outer join gets all possible rows, and the where clause keeps all rows where one or the other side are null and, where they match (neither null), keeps only those rows where the "lookup" values differ.

score 0 · Answer 3 · answered Jun 27 '19 at 05:57

Starting from SQL Server 2008 and also valid for Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

Following is the SQL queries;

USE [test]
GO

CREATE TABLE [dbo].[Student1](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Student2](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO

---- You can re-run from here with your data 
truncate table [Student1]
truncate table [Student2]

insert into [Student1] values (1, N'سید حیدر')
insert into [Student1] values (2, N'Syed Ali')
insert into [Student1] values (3, N'Misbah Arfin')

insert into [Student2] values (2, N'Syed Ali')
insert into [Student2] values (3, N'Misbah Arfin');

with StudentsAll (Id, [Name]) as
(
select s1.Id, s1.[Name] from Student1 s1
    left outer join Student2 s2 
on 
    s1.Id = s2.Id
),
StudentsMatched (Id, [Name]) as
(
select s1.Id, s1.[Name] from Student1 s1
    inner join Student2 s2 
on 
    s1.Id = s2.Id
)
select * from StudentsAll
except
select * from StudentsMatched

EXCEPT returns distinct rows from the left input query that aren't output by the right input query. [Reference](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017) — Syed, Jun 27 '19 at 06:04

Compare two tables, find missing rows and mismatched data

3 Answers3

Following is the SQL queries;

Linked