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?
(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
Joined: Jun 23, 2004
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.