Tuesday, April 10, 2012

SQL: how do you look for missing ids?

Suppose I have a table with lots of rows identified by a unique ID. Now I have a (rather large) user-input list of ids (not a table) that I want to check are already in the database.

So I want to output the ids that are in my list, but not in the table. How do I do that with SQL?

EDIT: I know I can do that with a temporary table, but I'd really like to avoid that if possible.

EDIT: Same comment for using an external programming language.

1 comment:

  1. Try with this:

    SELECT t1.id FROM your_list t1
    LEFT JOIN your_table t2
    ON t1.id = t2.id
    WHERE t2.id IS NULL