Simple field comparision

I don't know if this is exactly the right place for this, I have two tables in access database, each has the same "phone" (text) field. Table 1 is the customer list and table 2 is the restricted numbers list. Restricted Table has like 1.25 million records. Customer Table has like 20 thousand records.

I need to filter the customer records based on the restricted numbers adn generate a collection of allowed customers.

so I am doing a

select * from Customers where phone not in (select phone from RestrictedList)

and it is taking ages.

What is a better approach for efficiency in this case? I guess if I do anything outside the database (meaning copy the records to datastructures and do my own comparison) it would further increase the time. Any thoughts on this?

regards,
Brian

I think the solution is to make sure you have an index on 'phone' in your RestrictedList, and pass the value down into the inner query so it will short-circuit right away. Something like:

select * from Customers c where c.phone not in (select r.phone from RestrictedList r where r.phone = c.phone)

Query tuning can sometimes seem like voodoo, and what works for one DB might not help in another.

Having correct indexes, certainly on phone in RestrictedList, and probably also on Customers, is going to be essential.

Different queries to try:
[yours]
select * from Customers where phone not in (select phone from RestrictedList)

[Reid's]
select * from Customers c where c.phone not in (select r.phone from RestrictedList r where r.phone = c.phone)

[others]
( http://www.devx.com/dbzone/Article/9570/0/page/3 )
select * from Customers c where not exists (select r.phone from RestrictedList r where r.phone = c.phone)

(not sure of the outer join syntax for Access, this is at least close)
select c.*
from Customers c
left join RestrictedList r on (c.phone = r.phone)
where r.phone is null

(if and only if RestrictedPhone has the same columns as Customers)
select * from Customers c
minus
select * from RestrictedPhone r

(this might be slower, not faster, but you won't know until you test
I also don't know if Access allows sub-selects this way)
select c.* from Customers c,
(select phone from Customers minus select phone from RestrictedPhone) x
where c.phone = x.phone

Reid and Derby,

Thanks a lot for your response. I will try your suggestions. I am using a access database, so 'minus' operator is not an option.

regards,
Brian