[AccessD] "Not In" query speed

Actually I don't want either.  I am not using an IN() clause, nor NOT IN().
I am using an outer join TableA to TableB with a where clause "some field in
tableB is null".  I call that a "not in" query because it shows you where
records in TableA are "not in" TableB.


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Monday, October 29, 2007 11:05 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] "Not In" query speed

I clicked Send too quickly. I didn't realize that you wanted so many records
in your NOT IN() clause. For that many rows, I would do the join instead.

Arthur

On 10/29/07, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> 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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com