How get records not in another table?

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

I need to list the records in one table (8,000 rows) that are NOT in another
table (40,000 rows).
I tried SELECT ... WHERE NOT IN (SELECT...) but it took forever to run and I
had to cancel the query.
 
I need to list the records in one table (8,000 rows) that are NOT in another
table (40,000 rows).
I tried SELECT ... WHERE NOT IN (SELECT...) but it took forever to run and I
had to cancel the query.
Click to expand...

The Query design window has an "Unmatched Query" wizard which does exactly
this. To roll your own, use a "frustrated outer join" query like

SELECT <whatever you want to see>
FROM bigtable LEFT JOIN smalltable
ON bigtable.ID = smalltable.ID
WHERE bigtable.ID IS NULL;
 
John,

Small correction to your example. The Join was in the wrong direction, what
you posted would probably show no records (assuming that ID is a required field)

SELECT <whatever you want to see>
FROM bigtable RIGHT JOIN smalltable
ON bigtable.ID = smalltable.ID
WHERE bigtable.ID IS NULL;


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
OK guys, good suggestion but the problem I have now is that the column I am
joining can be null and is so in several records. When I do your join I get
the record I want but I also get these other records that have null in that
column.
 
Then apply criteria to screen out the null. Since Null is never equal to
anything you either have a zero-length string value or the null is in the
small table.

SELECT <whatever you want to see>
FROM bigtable RIGHT JOIN smalltable
ON bigtable.ID = smalltable.ID
WHERE bigtable.ID IS NULL

AND SmallTable.ID is Not Null



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top