[AccessD] "Not In" query speed

If the column being searched for matches is indexed, then SQL builds a
"table" in memory from your IN() clause. If the column is not indexed, SQL
has no choice but to do a full table scan.

A.

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
>