SQL: efficiently look up many IDs coming from outside the DB

Question

I need to look up thousands of rows by ID, but the IDs come from outside the DB, so I can't get them from other tables like so:

SELECT * FROM some_table WHERE ID IN ( SELECT KnownID FROM some_other_table WHERE someCondition )

Is there anything better than this:

SELECT * FROM some_table WHERE ID IN ('1001', '1002', '1003', ...)

Can I parametrize such a query where the number of id's isn't fixed? Or do I just end up concatenating a big string together each time?

(I'm using sqlite, btw, but I'm curious about other databases too.)

Answer 1

I would create a temp table with the variable values and do a "select were in".

Answer 2

(I'm using sqlite, btw, but I'm curious about other db's too.)

in sql server you could have a linked server and then just do a join between the two if it is on a different server. If it is on the same server but in different database then you would just do a regular join and add the dbname in from if the table and schema names

Answer 3

See my (accepted and implemented by the asker) answer here, to a very similar question.

Answer 4

what format are your input IDs arriving in?

Are they manually typed in by a user? in a text/csv/xml file? pulled in from a service ?

If you get them in bulk, you could insert them into a #temp table and then select against your #temp table... e.g.

//PSEUDO CODE
foreach(string s in ListOfIdsFromFile)
{
    //INSERT INTO #TempTable (ID) Values (ConvertToInt32(s))
}

Then

SELECT * FROM some_table a INNER JOIN #temp_table b ON a.ID = b.ID

Answer 5

SQL Server 2008 has a feature called Table Valued Parameters. See this article.

Answer 6

In SqlServer you can do something like this

SELECT * FROM some_table WHERE ID IN (SELECT ID1 UNION ALL SELECT ID2  UNION ALL SELECT ID3  UNION ALL SELECT ID4  UNION ALL SELECT ID4 )

Answer 7

Read this article by Erland Sommarskog