Re: Finding missing records

On Jan 27, 2006, at 08:59, Stefano B. wrote:

> select f1,f2,f3,f4 from table1 where (f1,f2,f3,f4) NOT IN (select
> f1,f2,f3,f4 from table2)
>  
> but it seems not work (as I want). It returns me no records. If I use
> the IN clause it returns me all 10000 table1 records.

The standard way to do this is:

select f1,f2,f3,f4 from table1
except
select f1,f2,f3,f4 from table2;

Note that IN and EXCEPT are essentially set operators - if you have
duplicates in either table, you might not get what you expect. Your
last comment above seems to indicate that this is indeed the case.

If what you want is the =bag= difference of the two tables, you'll have
to do something more complicated. Possible solutions might involve
counting duplicates in both tables with a COUNT(*) and GROUP BY, and
then joining on the four columns and subtracting the counts.

- John D. Burger
MITRE