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

From: Bank system research group <oracle_at_sg3.kbv>
Date: 25 Feb 93 02:00:44 GMT
Message-ID: <AACX2ZhiH6_at_sg3.kbv>

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
---CUT HERE---

set feedback on
set echo on
set pause off

spool tab

CREATE TABLE tbl (

   a NUMBER,
   b NUMBER
) ;

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 ) ;
DROP TABLE tbl ;

EXIT; spool off
---CUT HERE---
It produces the output below:
---CUT HERE---

13:31:43 SQL>
13:31:43 SQL> CREATE TABLE tbl (
13:31:43   2     a NUMBER,
13:31:43   3     b NUMBER
13:31:43   4  ) ;

Table created.

13:31:43 SQL> INSERT INTO tbl (a,b) VALUES (3,2) ;

1 record created.

13:31:43 SQL> INSERT INTO tbl (a,b) VALUES (2,1) ;

1 record created.

13:31:43 SQL> INSERT INTO tbl (a,b) VALUES (1,NULL) ;

1 record created.

13:31:44 SQL> SELECT * FROM tbl ;

         A B
---------- ----------

         3          2
         2          1
         1

3 records selected.

13:31:44 SQL> SELECT * FROM tbl WHERE a NOT IN (
13:31:44   2     SELECT b from tbl
13:31:44   3  ) ;

no records selected

13:31:44 SQL> SELECT * FROM tbl WHERE a NOT IN (
13:31:44   2     SELECT b from tbl WHERE b IS NOT NULL
13:31:44   3  ) ;

         A          B


---------- ----------


         3          2

1 record selected.

13:31:44 SQL> DROP TABLE tbl ;

Table dropped.

13:31:44 SQL>
13:31:44 SQL> EXIT;
---CUT HERE---
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

---

Received on Thu Feb 25 1993 - 03:00:44 CET