Problem with subquery

[KnightDeveloper]

I'm having trouble figuring out what's wrong with my SQL query. If I execute the following, which returns a product id from one table that is also found in another products table, then it works properly.

SELECT prod_id from products1 WHERE products1.prod_id IN (SELECT prod_id FROM products2)

However what I really want to do is to return products that do not appear in the second table. I've tried the following but none of them work:

SELECT prod_id from products1 WHERE products1.prod_id NOT IN (SELECT prod_id FROM products2)

SELECT prod_id from products1 WHERE products1.prod_id <> ANY (SELECT prod_id FROM products2)

SELECT prod_id from products1 WHERE products1.prod_id <> ALL (SELECT prod_id FROM products2)

Can anyone help me figure what's wrong?

[svidgen]

Try ... SELECT prod_id from products1 left join products2 on (products1.prod_id=products2.prod_id) WHERE products2.prod_id is null;

[triassic]

Your NOT IN query should be correct. I just tried something similar and it worked. I don't know why it wouldn't be working for you.

This is another way to do it using a join, but watch out for duplicates if the tables aren't one-to-one.
SELECT products1.prod_id from products1 LEFT JOIN products2 ON (products1.prod_id=products2.prod_id) WHERE products2.prod_id IS NULL