Tag: NOT IN

NOT IN vs. NOT EXISTS when NULL values are involved

Posted: 2008-06-04   |   Categories: Database, Oracle, Programming   |   Tags: NOT EXISTS, NOT IN,

NOT IN does not behave as you would expect when NULL values are involved. Suppose you have the following tables:

Now try the following query:

   
SELECT *
FROM table_a a
WHERE a.cd_field NOT IN (SELECT b.cd_field FROM table_b b);

Does the previous query return any row? The answer is NO it doesn’t, even if there is a value (2) in table_a.cd_field which is not present in table_b.cd_field.