Combining a large number of conditions in SQLite WHERE clause

Question

I need to retrieve records that match IDs stored in a list. The query, generated at runtime, is simple:

SELECT [whatever FROM sometable] WHERE (id = 1) or (id = 5) or (id = 33).

Which is equivalent to

SELECT [whatever FROM sometable] WHERE [id] IN (1, 5, 33);

This is fine, but what if the list contains hundreds or thousands of IDs? The statement will be huge and at some point the SQL parser might croak, or if it does not, performance will probably be quite bad. How can I do this in a way that is not so sensitive to the number of records being retrieved?

(The reason I can't just loop over the list and retrieve records one by one is that I need the database to do ORDER BY for me. Records must come from the DB ordered by a specific field, while the list represents records selected by the user in a grid which can be sorted in any number of ways. And yes, I could sort the records in code after I retrieve them, but that's plan B, since I don't even need to hold them all in one data structure, only to come properly ordered.)

Answer 1

If you're really going to have so many IDs that you're worried about the SQL parser croaking, you can store them into a temporary table and do a cross-join.

Simply create the table with one (primary key) column, the ID, then populate it with the desired IDs and use something like:

SELECT [whatever] FROM [sometable] st, [idtable] it
WHERE st.id = it.id

That query won't choke any parser and the rows retrieved will be limited to those having the ID in the temporary table.

This doesn't have to be a temporary table, of course, you can leave it lying around provided you ensure only one "thing" uses it at a time.