SQL: check to see which id's already exist

Question

Let's say I have a list of IDs and I want to see if rows already exist with those IDs in my SQL database. Is there a single SQL command that I can issue to get back the subset of the ID list that's not already represented in the database? I could iterate over each ID in my code and issue a separate SQL check for each ID, but that sounds inefficient.

I'm trying to do this against an sqlite database. thanks!

Answer 1

Drop the IDs into a table and then try:

SELECT C.id
FROM checkids C
LEFT JOIN mytable M
ON M.id = C.id
WHERE M.id IS NULL

Answer 2

You could use the IN operator; for instance, to test for ids 1, 2, 8, and 42, you'd do:

SELECT id
FROM tbl
WHERE id IN (1, 2, 8, 42);