I was debugging a stored procedure the other day and found some logic something like this:
WHERE idcode <> (SELECT ids FROM tmpIdTable)
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.