How to find duplicates from two tables and also to find duplicate in itself?

Question

I have created this statement in access 2003

  SELECT COUNT(*)
  FROM TABLEA
  WHERE NOT EXISTS(SELECT * FROM TABLEB);

Does this statement helps to check if the records in table A is the same as table b? TABLEA is the new table of table b and i want to make sure that all records from table b are in table A.

Secondly i have this table TABLEC. How can i check if there are duplicate records, meaning all the fields values are the same, in TABLEC?

Solution

The answer is: No, your query does not make sense.

To tell whether two records are 'same', you have to define the term 'equal'. Should all fields be equal? Or only certain fields?

If you have two Tables TableA and TableB and they have two fields 'A' and 'B', then this statement finds all records which exist in both tables:

select distinct TableA.* 
  from TableA 
       join TableB 
         on TableA.A = TableB.A 
        and TableA.B = TableB.B

Or

select * 
  from TableA
 where exists (
     select 1 
       From TableB 
      where TableA.A = TableB.A 
        and TableA.B = TableB.B
     )

Edit: User 10e5x pointed out that his table contains NULL values. So the comparison per field has to be a bit more complicated to compensate the NULL comparison caveats.

I will just give the WHERE part:

where TableA.A = TableB.A or coalesce (TableA.A, TableB.A) is NULL 
  and TableA.B = TableB.B or coalesce (TableA.B, TableB.B) is NULL

The function coalesce(a,b,c...) returns the leftmost non NULL value, hence

coalesce (A,B) is NULL
-- is equal to
A is NULL and B is NULL

Note: This tricky coding is a reason why you should avoid NULL values in columns which are used for comparison.

Comments

[10e5x]

Ya all fields have to be equal to be consider duplicate. If i have 45 columns i need to do the where clause with 45 comparison?

[alzaimar]

Yes. You have to test each of the 45 fields.

[10e5x]

hi wonder if u are there. I tried your method. It return me none of the records are the same. Then i do a check, most of the records are totally the same. I have two columns which are null, is it becuz of that?

[alzaimar]

Comparing NULL values is tricky, because NULL does not compare to anything, not even to NULL. I will edit my proposal to compensate NULL values.

[bitoiu]

if you're not sure if this is a valid go, add it as a comment please.