NOT IN

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.