Find all differences between 2 tables

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

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 15:55:26

Look up the except set operator

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources