In the research group where I work, we must solve following problem hundreds, maybe thousands of times every day: given a set of putative gene names (typically a few hundred of them), flag those that are not in our (MySQL) database. This problem is solved in a number of ways by our various applications and scripts. I would like to optimize the process.

The simplest approach, of course, is to iterate over the list of gene names (after removing any duplicates, of course), and for each gene name perform something like

SELECT count(*) FROM gene WHERE genename = 'GENENAME-XYZ';



and detect the condition where the search fails.

This means performing one such query for usually hundreds, often thousands, of genes.

(BTW, most of these queries are usually performed by scripts written in Python, R, Perl, MATLAB, etc. using those language's DBI facilities.)

Alternatively, one could concoct a (typically huge) SQL statement of the form

SELECT DISTINCT genename FROM gene \
    WHERE genename IN ('GENENAME-001', 'GENENAME-002', ..., 'GENENAME-789');

...and from the result deduce the subset of invalid gene names.

Maybe it's just me, but I pick up some potent code smell from such huge SQL statements.

Bottom line, I don't have an entirely satisfactory solution, but since the problem must be very common, I figure that there must be standard solutions for it. If this is correct, please let me know what they are.

BTW, assume that access to the DB is read-only. In particular, please rule out any solution that involves creating temporary tables.

Also, in case it matters, ours is a MySQL database.


Enter the names into table x.

The list of all "New names":

SELECT x.name
    FROM x
    LEFT JOIN gene  ON x.name = gene.name
        WHERE gene.name IS NULL;

gene should have an index on name.

I don't think it can be done with just an IN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.