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

From: <sstephen_at_us.oracle.com>
Date: Sat, 27 Feb 1993 06:42:59 GMT
Message-ID: <1993Feb26.224259.1_at_us.oracle.com>


In article <AACX2ZhiH6_at_sg3.kbv>, oracle_at_sg3.kbv (Bank system research group) writes:
> Hi Oracle men !!!
>
> I've encountered a little problem with Oracle RDBMS v.6.0.27.9.2
> It follows: try to run this SQL scrypt in SQL*Plus
 [deleted some unecessary details]
> INSERT INTO tbl (a,b) VALUES (3,2) ;
> INSERT INTO tbl (a,b) VALUES (2,1) ;
> INSERT INTO tbl (a,b) VALUES (1,NULL) ;
> SELECT * FROM tbl ;
> SELECT * FROM tbl WHERE a NOT IN (
> SELECT b from tbl
> ) ;
> SELECT * FROM tbl WHERE a NOT IN (
> SELECT b from tbl WHERE b IS NOT NULL
> ) ;
> A B
> ---------- ----------
> 3 2
> 2 1
> 1
>

This is a classic SQL problem. Let me expand the last 2 SQL statements :

SELECT * FROM tbl WHERE a NOT IN (SELECT b from tbl); -> SELECT * FROM tbl where a NOT IN (2,1,NULL); -> SELECT * FROM tbl where a NOT = ANY (2,1,NULL); /* an equivalent */

When a=3, it applies "!=" to each element, if all are true, then return the row. (ie. NOT = ANY --> ALL (NOT = ))

3 != 2, true
3 != 1, true
3 != NULL, false!!! /* remember that "= NULL" and "!= NULL" are both false */
... therefore the row is not returned.

> It seems to me the second SELECT should return the same set of records
> as the third one. You see it doesn't !!!
>
> My question is:
> Do I not understand SQL language semantics OR it is an Oracle BUG ???
>
> Any opinions and suggestions would be appreciated.
>
> ======================================================================
> Alex Novicky | Vladivostok | Russia | voice phone (Russia)(4232)266996
> ======================================================================

-- 

In all cases, if any element of a NOT IN list is NULL, it will never return a
row.  Adding the ... WHERE b IS NOT NULL ... was the correct way to structure
this logic.

================================================================================
Scott Stephens				inet:	sstephen.us.oracle.com
Oracle WorldWide Support                Redwood City, California
Received on Sat Feb 27 1993 - 07:42:59 CET

Original text of this message