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

From: Guy Vilquin <vilquin_at_cenatls.cena.dgac.fr>
Date: Thu, 25 Feb 1993 08:33:25 GMT
Message-ID: <1993Feb25.083325.3991_at_cenatls.cena.dgac.fr>

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
|> It follows: try to run this SQL scrypt in SQL*Plus
|> ......
|> 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 !!!

The second select could be written as:

select * from tbl where a<>2 and a<>3 and a<>null

In oracle, every condition like ' column operator null' with operator different from 'is' or 'is not' is always false. Then the condition 'a<>2 and a<>3 and a<>null' is always false and the select returns no rows.

---

Guy Vilquin
Centre d'Etudes de la Navigation Aerienne TOULOUSE
France

---

Received on Thu Feb 25 1993 - 09:33:25 CET