How get records not in another table?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mscertified - 24 Apr 2008 01:53 GMT

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.

Reply to this Message

John W. Vinson - 24 Apr 2008 02:25 GMT

>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.

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;

Signature

John W. Vinson [MVP]

Reply to this Message

John Spencer - 24 Apr 2008 13:21 GMT

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

>> I need to list the records in one table (8,000 rows) that are NOT in another
>> table (40,000 rows).
[quoted text clipped - 8 lines]
> ON bigtable.ID = smalltable.ID
> WHERE bigtable.ID IS NULL;

Reply to this Message

John W. Vinson - 24 Apr 2008 17:13 GMT

>The Join was in the wrong direction

oops....

Thanks John!

Signature

John W. Vinson [MVP]

Reply to this Message

mscertified - 24 Apr 2008 17:43 GMT

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.

> >The Join was in the wrong direction
>
> oops....
>
> Thanks John!

Reply to this Message

John Spencer - 24 Apr 2008 18:14 GMT

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

> 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
[quoted text clipped - 5 lines]
>>
>> Thanks John!

Reply to this Message