I have local data with id's from an external database. I want to then select data from that external database using those id so I do something like:
SELECT * FROM table WHERE id IN (:listofids)
WHERE id IN (SELECT id FROM ...)
in () OR in () OR in()
What are my options here besides what I am doing in the above?
They are limited - you could chain a LONG sequence of
OR id = X calls but you may have SIGNIFICANT performance problems compared to
The best option performance-wise is to join to a table (parameter, temporary, CTE, inline
UNION, or static) on the server side. However that's not always an option depending on how you're executing queries and what permissions you have.
Of course, you can also execute a separate query for each value, which would be easier to code but might have performance problems as well.
Are the limitations of max items set at the DB level, the type of DB, what?
That is platform-specific so there's not a universal answer
Should I do in () OR in () OR in(). That doesn't seem too slick. Are all of these options viable?
That is one option to add to the others above - you'd have to try it to see what the performance impact is.
I've read a bit about possibly using temp tables to do this but without any examples. How would I load data into a temp table to then join into the table I'm wanting to get the data from?
That depends on the platform, your permissions, your API available, etc.