[AccessD] "Not In" query speed

jwcolby jwcolby at colbyconsulting.com
Mon Oct 29 09:16:08 CDT 2007


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 




More information about the AccessD mailing list