"<>" vs "NOT IN"
SELECT something
FROM someTable
WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)
checks against any value in the list.
However, the NOT IN is not NULL-tolerant. If the sub-query returned a set of values that contained NULL, no records would be returned at all. (This is because internally the NOT IN is optimized to idcode <> 'foo' AND idcode <> 'bar' AND idcode <> NULL
etc., which will always fail because any comparison to NULL yields UNKNOWN, preventing the whole expression from ever becoming TRUE.)
A nicer, NULL-tolerant variant would be this:
SELECT something
FROM someTable
WHERE NOT EXISTS (SELECT ids FROM tmpIdTable WHERE ids = someTable.idcode)
EDIT: I initially assumed that this:
SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)
would check against the first value only. It turns out that this assumption is wrong at least for SQL Server, where it actually triggers his error:
Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
try this, may run faster because of index usage:
SELECT something
FROM someTable
LEFT OUTER JOIN tmpIdTable ON idcode=ids
WHERE ids IS NULL