Search code examples
mysqlsqlcsvjoinsubquery

Get subset of rows based on a list of primary keys in file

I have a large (1000s) list of IDs in a text file. I want to return the rows from my database that correspond to these IDs. How can I do this?

My current method is to simply paste the whole list into a gigantic SQL query and run that. It works, but I feel like there must be a better way.

Solution

  • As the list of values goes bigger and bigger, a better solution is to load it into a table, that you can then use it your query. In MySQL, the load data statement syntax comes handy for this.

    Consider something like:

    create temporary table all_ids (id int);
load data infile 'myfile.txt' into table all_ids;
create index idx_all_ids on all_ids(id);  -- for performance

select t.*
from mytable t
where exists (select 1 from all_ids a where a.id = t.id)

    The load data syntax accepts many options to accommodate the format of the input file - you can read the documentation for more information.