Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Find all differences between 2 tables

Author  Topic 

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-03-05 : 11:44:47
I have this 2 tables:
Table1:      Table2:

ID Value ID Value
--------- ---------
1 2 2 1
2 1 3 4
4 3 4 4
I need to find all differences between the 2 Tables.

A mismatch can be:
- Table1.ID exists, Table2.ID does not exist
- Table2.ID exists, Table1.ID does not exist
- Table1.ID = Table2.ID and Table1.Value <> Table2.Value

So I want to be returned:
ID  Value  ID  Value
--------------------
1 2 NULL NULL
4 3 4 4
NULL NULL 3 4
There must be something simpler than the solution I came up with which is:
select * from Table1 a full outer join Table2 b on a.Id = b.Id
WHERE a.value <> b.value or (a.value IS NULL and b.value IS NOT NULL) or (a.value IS NOT NULL and b.value IS NULL)
Martin

Hommer
Aged Yak Warrior

808 Posts

Posted - 2015-03-05 : 12:10:13
Check this out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054
Go to Top of Page

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-03-05 : 12:31:56
Interesting link, seems rather complex and I didn't get it after looking at it for 2 minutes... So I rather stick to my query, which does the job and compared to what is discussed in that thread, is easy to understand. Maybe performance is bad, but I can live with it. Thanks anyway!

Martin
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 15:55:26
Look up the except set operator
Go to Top of Page

magbenyezi
Starting Member

3 Posts

Posted - 2015-03-11 : 11:01:29
;WITH TABLE1 AS(
SELECT TABLE1.ID id
TABLE1.VALUE val
FROM TABLE1
),TABLE2 AS(
SELECT TABLE2.ID id
TABLE2.VALUE val
FROM TABLE2
)
SELECT t1.id, t1.val, t2.id, t2.val
FROM TABLE1 t1 FULL JOIN TABLE2 t2 on t1.id = t2.id
WHERE t1.val <> t2.val


MA
Go to Top of Page
   

- Advertisement -