NOT IN vs. NOT EXISTS when NULL values are involved
NOT IN does not behave as you would expect when NULL values are involved. Suppose you have the following tables:
CD_FIELD DS_FIELD 1 ALPHA 2 BETA **TABLE_A** CD_FIELD DS_FIELD 1 GAMMA NULL DELTA **TABLE_B** 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.