[AccessD] "Not In" query speed

The only information I have is logic and a long ago session with a SQL
guru.  NOT IN will always be slower because it has to examine ever
record in the dataset to return a value.  IN is faster because it only
needs to examine records until it finds a match.  Of course, if the
match is in the last record, then there's no difference in speed.  LOL

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, October 29, 2007 7:16 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] "Not In" query speed

Does anyone have any information on the speed of a "not in" query?  That
is where an outer join to a table is used and then a filter set on the
PK (or any field really) of the outer joined table to discover all
records "not in"
the outer joined table?

Is the speed the same as an inner join would have been?  Is there more
overhead because of the where clause?  

In other words, assume a table where there are 50K claim records in
tblClaim.  
Assume that there are 25K records in TblClaimLogged (exactly 1/2 the
number of records in tblClaim) and that each ClaimID is only in
tblClaimLogged one time.  

Now do an inner join to discover which records are IN tblClaimLogged.  
Now do an outer join to discover which records are NOT IN
tblClaimLogged.  

Both queries should return exactly 25K records since exactly 1/2 of the
records in tblClaim are in tblClaimLogged, and each record can only be
in there once.

Do the two queries return the result sets in the same amount of time?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com