What is the best way to match over 10000 different elements in database?

Question

Ok here's my scenario:

Programming language: Java

I have a MYSQL database which has around 100,000,000 entries.

I have a a list of values in memory say valueList with around 10,000 entries.

I want to iterate through valueList and check whether each value in this list, has a match in the database.

This means I have to make atleast 10,000 database calls which is highly inefficient for my application. Other way would be to load the entire database into memory once, and then do the comparison in the memory itself. This is fast but needs a huge amount of memory.

Could you guys suggest a better approach for this problem?

EDIT :

Suppose valueList consists of values like : {"New","York","Brazil","Detroit"}

From the database, I'll have a match for Brazil and Detroit. But not for New and York , though New York would have matched. So the next step is , in case of any remaining non matched values, I combine them to see if they match now. So In this case, I combine New and York and then find the match.

In the approach I was following before( one by one database call) , this was possible. But in case of the approach of creatign a temp table, this wont be possible

Answer 1

You could insert the 10k records in a temporary table with a single insert like this

insert into tmp_table (id_col)
values (1),
       (3),
       ...
       (7);

Then join the the 2 tables to get the desired results.

I don't know your table structure, but it could be like this

select s.* 
from some_table s
inner join tmp_table t on t.id_col = s.id