Efficient sqlite query based on list of primary keys

Question

For querying an sqlite table based on a list of IDs (i.e. distinct primary keys) I am using following statement (example based on the Chinook Database):

SELECT * FROM Customer WHERE CustomerId IN (1,2,3,8,20,35)

However, my actual list of IDs might become rather large (>1000). Thus, I was wondering if this approach using the IN statement is the most efficient or if there is a better/optimized way to query an sqlite table based on a list of primary keys.

Answer 1

If the number of elements in the IN is large enough, SQLite constructs a temporary index for them. This is likely to be more efficient than creating a temporary table manually.

The length of the IN list is limited only be the maximum length of an SQL statement, and by memory.