Mysql – From a set of values, how to find the values not stored in a table’s column

MySQL

I have a table which will potentially store hundreds of thousands of integers

desc id_key_table;

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id_key         | int(16)      | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

From a program, I have a large set of integers. I'd like to see which of these integers are NOT in the above id_key column.

So far I've come up with the following approaches:

1) Iterate through each integer and perform a:

select count(*) count from id_key_table where id_key = :id_key

When count is 0 the id_key is missing from the table.

This seems like a horrible, horrible way to do it.

2) Create a temporary table, insert each of the values into the temporary table, and perform a JOIN on the two tables.

create temporary table id_key_table_temp (id_key int(16) primary key );

insert into id_key_table_temp values (1),(2),(3),...,(500),(501);

select temp.id_key
from id_key_table_temp temp left join id_key_table as main 
         on temp.id_key = main.id_key 
where main.killID is null;

drop table id_key_table_temp;

This seems like the best approach, however, I'm sure there is a far better approach I haven't thought of yet. I'd prefer to not have to create a temporary table and use one query to determine which integers are missing.

Is there a proper query for this type of search?

(MySQL)

Best Answer

Your second solution using the LEFT JOIN is by far the best approach. I would not use a temporary table, I'd use a regular table and populate it with new values any time you wanted run the query.

Related Question