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

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

爱的故事 发布于 2021-11-28 字数 645 浏览 945 回复 5 原文

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.)

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

请恋爱 2022-06-07 5 楼

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 )
a√萤火虫的光℡ 2022-06-07 4 楼

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

追我者格杀勿论 2022-06-07 3 楼

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
∞觅青森が 2022-06-07 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

携君以终年 2022-06-07 1 楼

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