Finding keys in a db table that arent in a list

Question

On sybase. I've got a load of primary keys

If I do:

select key from table where key in (...list of about 2000 keys...)

I get about 1700 results. Whats the easiest way to get a list of the ~300 entries in my list that arent present?

It seems to be something that should be easy, but I cant work it out...

EDIT: Seems an example may be in order

If my table has keys 1,2,4 and my list is 1,2,3 then I want a query that will give me the answer 3 - the element in my list that isnt in the table. If I use 'NOT IN' then I get the answer 4, which is wrong.

Answer 1

I think you'll need to put your elements into a table of their own to make them selectable. It's plain sailing after that...

SELECT key
    FROM my_new_table
    WHERE key NOT IN (SELECT key
                          FROM Table);

(or any speedier version of the above query).

Answer 2

I think your answer is actually resolved by @BrianHooper solution, but your question is lacking clarification.

You mentioned a "list" of numbers ... contiguous or not is not applicable. In response to my comment, you never stated you had two tables...

So, it appears just for the sake and ease of following: You have a master inventory table with 2000 items in it. Additionally, you have a sales activity table of all inventory that has ever sold... This table could have 10,000 lines in it, of which would be multiple sales of many given items... However, SOME items have NEVER been sold within the 10,000 sales. You want to know which items NEVER sold out of the 2000 in inventory...

My query will be based on the above scenario, but you'll have to pair-up with your actual scenario.

select   
      MyItemID
   from 
      MyInventoryTable
   where 
      MyItemID NOT IN 
              ( select SoldItemID
                   from SalesData );