What would be the best way to write this query

Question

I have a table in my database that has 1.1MM records. I have another table in my database that has about 2000 records under the field name, "NAME". What I want to do is do a search from Table 1 using the smaller table and pull the records where they match the smaller tables record. For example Table 1 has First Name, Last Name. Table 2 has Name, I want to find every record in Table 1 that contains any of Table 2 Names in either the first name field or the second name field. I tried just making an access query but my computer just froze. Any thoughts would be appreaciated.

Answer 1

Be sure to create indexes on Table1.first_name, Table1.last_name, and Table2.name. They will dramatically speed up your query.

Edit: For Microsoft Access 2007, see CREATE INDEX.

Answer 2

See above previous notes about indexes, but I believe from your description, you want something like:

select table1.* from table1 
  inner join 
    table2 on (table1.first_name = table2.name OR table1.last_name = table2.name);

Answer 3

It should go something like this,

Select Table1.FirstName, Table1.LastName 
from Table1 
where Table1.FirstName IN (Select Distinct Name from Table2) 
   or Table1.LastName  IN (Select Distinct Name from Table2)

And there are various other ways to run this same query, i would suggest you see execution plan for each of these queries to find out which one is the fastest. In addition creating indexes on the column which is used in a "where" condition will also speed up the query.

Answer 4

have you considered the following:

Select Table1.FirstName, Table1.LastName 
from Table1 
where EXISTS(Select * from Table2 WHERE Name = Table1.FirstName) 
   or EXISTS(Select * from Table2 WHERE Name = Table1.LastName)

I have found before that on large tables this might work better than an inner join.

Answer 5

i agree with astander. based on my experience, using EXIST instead of IN is a lot faster.