Re: SELECT ... WHERE NOT IN ( SELECT ... ) problem

In article <AACX2ZhiH6_at_sg3.kbv>, oracle_at_sg3.kbv (Bank system research group) writes:
|> I've encountered a little problem with Oracle RDBMS v.6.0.27.9.2
|> SQL> SELECT * FROM tbl ;
|>
|> A B
|> ---------- ----------
|> 3 2
|> 2 1
|> 1
|>
|> 3 records selected.
|>
|> SQL> SELECT * FROM tbl WHERE a NOT IN (SELECT b from tbl);
|>
|> no records selected
|>
|> SQL> SELECT * FROM tbl WHERE a NOT IN (SELECT b from tbl

         WHERE b IS NOT NULL);
|>
|> A B
|> ---------- ----------
|> 3 2
|>
|> 1 record selected.
|>
|> It seems to me the second SELECT should return the same set of records
|> as the third one. You see it doesn't !!!

The answer to this can be found in the V6 SQL Reference Guide p.4-3 or the V7 SQL Reference Guide p.3-5.

Your queries can be simpified as
SELECT * FROM tbl WHERE a NOT IN (2, 1, null); SELECT * FROM tbl WHERE a NOT IN (2, 1); Oracle treats NOT IN as the equivalent to "!=ALL" and if any member of the set is NULL the expression will evaluate to FALSE (ie a != NULL is UNKNOWN which we treat as a FALSE).

Roderick Manalac
Oracle Corporation Received on Sat Feb 27 1993 - 06:38:17 CET