Find differences in record sets

Question

I want to know if 2 record sets are different. I use a full outer join for that. Simple example:

select count(*) from 
(
 select 1 as c
 union 
 select 2 as c 
) t1
full outer join 
(
 select 1 as c 
 union 
 select 3 as c
) t2 on t1.c = t2.c
where t1.c is null or t2.c is null

SQLFiddle

If the record sets are equal then the count() returns 0.

Is there a better way to achieve that?

May we assume that `c` is a unique column in each record set? — Sergey Kalinichenko, Nov 13 '13 at 16:34
[***Outer Results LINK***](http://www.sqlusa.com/bestpractices/nonmatchingrows/) I think these are your two best options; 1) the way you have written -or- 2) `EXCEPT` method — Eric J. Price, Nov 13 '13 at 23:25

score 1 · Answer 1 · answered Nov 13 '13 at 16:38

If c in your record sets are unique, you can do it using GROUP BY and HAVING, like this:

SELECT COUNT(*) FROM (
    SELECT c FROM (
        SELECT c FROM record_set_1
    UNION ALL
        SELECT c FROM record_set_2
    ) all_data
    GROUP BY c
    HAVING COUNT(*) <> 2
) non_matched

SQLFiddle

score 0 · Answer 2 · answered Nov 13 '13 at 16:41

You could use EXISTS:

IF EXISTS(SELECT null FROM t1 where c NOT IN (SELECT c FROM t2))
OR EXISTS(SELECT null FROM t2 where c NOT IN (SELECT c FROM t1))

Of course that doesn't tell you which records do not match - if you want to know that your original query is just fine.

Find differences in record sets

SQLFiddle

2 Answers2

SQLFiddle