NOT IN is not the inverse of IN

[ngreenleaf]

I have written a rather simple query. It is:

select field1 from table1
where field1 not in (select field2 from table2);

Note that field 2 is the foreign key to field1. The query is a little more complex than this, but I can reduce it to this and the same problem results.

This query returns nothing. When I run the same query replacing NOT IN with IN, I get the proper values returned. There is definitely more than one value that meets the NOT IN criteria. When I replace the subquery with a list of the values it returns (eg, NOT IN ('abc','def','ghi')) - then the query returns the proper values. When I run "select field2 from table2" I get the values I expect to see.

Does anyone have any suggestions about this condition. I have been using the NOT IN operator successfully in many other queries.

 

[r937]

if you have a pair of sample tables with values that demonstrate this problem, please post the DDL to create the tables and the DML to insert the values, so that we can test this ourselves

there's gotta be something else going on


rudy

 

[ngreenleaf]

Opps... the answer is because there is a NULL in the set of values returned. Still seems odd that this would happen, but if I add "and field2 is not null" to the subquery it works.

What a silly thing to do... answer your own question almost immediately! IT just happened that way.

 

[r937]

yeah, that'll do it

good old nulls, eh?

gotta love 'em