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?

Answer 1

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.