How to find difference in rows for two tables in DB2 or SQL Server

Question

In the table1 I have 1421144 rows and table2 has 1421134 rows.

I tried this query, but I don't get any rows returned.

select table1.ID 
from table1
where ID not in (select ID from table2)

I have also used this query:

select ID from table1
except
select ID from table2

But I don't get any rows. Please help me, if the table1 has duplicates how can I get those duplicates?

Is the ID column on both tables actually a unique key? Or can you have two records with Id=1, for example? — BenM, Feb 15 '18 at 16:00
SELECT T1.ID FROM Table1 T1 LEFT JOIN Table2 T2 ON T2.Id = T1.Id WHERE T2.Id IS NULL -- This will get you all records in T1 that dont exist in T2, assuming the answer to my first question is "yes" — BenM, Feb 15 '18 at 16:01
Yes the ID's in the two tables are same and unique. In fact all the columns in both the tables are same. — user7463647, Feb 15 '18 at 16:06
I have tried the above query and the result is 0 rows. @BenM — user7463647, Feb 15 '18 at 16:08
The only way that's possible is if there are duplicate records (with duplicate ID's). Try Gordon's second query in his answer, with the GROUP BY: select t1.id, count(*) from t1 group by t1.id having count(*) > 1; — BenM, Feb 15 '18 at 16:09
Easy to find difference by table or fields. https://stackoverflow.com/questions/48066934/difference-between-data-values-in-two-db2-tables-with-different-table-structures/48068108#48068108 — danny117, Feb 15 '18 at 22:14

score 0 · Accepted Answer · answered Feb 15 '18 at 15:56

Assuming ids are unique, you can use full outer join in either database:

select coalesce(t1.id, t2.id) as id,
       (case when t1.id is null then 'T2 only' else 'T1 only' end) 
from t1 full outer join
     t2
     on t1.id = t2.id
where t1.id is null or t2.id is null;

It is quite possible that the two tables have the same sets of ids, but there are duplicates. Try this:

select t1.id, count(*)
from t1
group by t1.id
having count(*) > 1;

and

select t2.id, count(*)
from t2
group by t2.id
having count(*) > 1;

When I try the above query 1 no rows are returned. But when I tried the above query 2 and 3. I got 10 rows difference again with 36687 rows in table 1 and 36677 rows in table2 @Gordon Linoff — user7463647, Feb 15 '18 at 16:18

score 0 · Answer 2 · answered Feb 15 '18 at 15:59

If you have duplicates, try:

WITH Dups AS(
    SELECT ID, COUNT(ID) OVER (PARTITION BY ID) AS DupCount
    FROM Table1)
SELECT *
FROM Dups
WHERE DupCount > 1;

If you need to delete the dups, you can use the following syntax:

WITH Dups AS(
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS DupCount
    FROM Table1)
DELETE FROM Dups
WHERE DupCount > 1;

Obviously, however, check the data before you run a DELETE statement you got from a random on the internet. ;)

score 0 · Answer 3 · answered Feb 15 '18 at 16:16

I Guess u have data type mismatch between 2 tables, cast them to integers and try your first query

select table1.ID from table1
where cast(ID as int) not in (select cast(ID as int) from table2)

If you have stored in a different format than int, cast them to varchar and try with this datatype.

Not in takes longer to execute, use left join instead

select t1id from 
(
select t1.id t1Id, t2.Id t2Id from  table1 left join table2
on cast(t1.id as int) = cast(t2.id as int) 
) x where t2Id is null

How to find difference in rows for two tables in DB2 or SQL Server

3 Answers3