Re: Simple Query

Bhalchandra Ghatate (bhala_at_cs.utexas.edu) wrote:

: Now is this a bug: ?
: junk is a table with one column of type varchar(2).
 

: ORACLE:
:
: SQL> select * from junk;
:
: NAME
: -------------------------
: 1
: 2
: 3
: 4
:
: SQL> select * from junk where name not in ('1','2','');
:
: no rows selected
 

: where are 3 and 4.

'' is interpreted by Oracle as NULL I believe, so your query is actually

select * from junk where name not in ('1','2',NULL)

which delivers no rows.

In short never use NULLs in combination with other operators than IS NULL and IS NOT NULL, they will always return no rows selected.

Teijo Doornkamp (doornkampt_at_vertis.nl) Received on Tue Feb 07 1995 - 12:33:53 CET