Path: news.easynews.com!easynews!newsfeed-east.nntpserver.com!nntpserver.com!news.maxwell.syr.edu!intgwpad.nntp.telstra.net!news.telstra.net!newsfeeds.bigpond.com!not-for-mail
Message-ID: <3D5327ED.AC92D4DF@oracle.com>
From: Richard Foote <Richard.Foote@oracle.com>
Organization: Oracle Corporation
X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.misc
Subject: Re: Easy SQL problem
References: <6d04baec.0208081547.5aa3527e@posting.google.com>
Content-Type: multipart/mixed;
 boundary="------------1394B3AADE645B63362978E9"
Lines: 114
Date: Fri, 09 Aug 2002 12:24:45 +1000
NNTP-Posting-Host: 144.138.154.166
X-Trace: newsfeeds.bigpond.com 1028859782 144.138.154.166 (Fri, 09 Aug 2002 12:23:02 EST)
NNTP-Posting-Date: Fri, 09 Aug 2002 12:23:02 EST
Xref: easynews comp.databases.oracle.misc:85352
X-Received-Date: Thu, 08 Aug 2002 19:27:33 MST (news.easynews.com)
--------------1394B3AADE645B63362978E9
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Hi Ben,

The nice thing about an easy SQL problem is that generally it's an easy
SQL solution.

What you are highlighting is not a 9i feature but a characteristic of
how Oracle deals with nulls since time began.

Logically, the best way to view a null is as an unknown. Therefore if
you ask if one unknown value is equal to (or not equal to) another
unknown value the answer of course is you don't know.

Therefore a condition variable = null can never be treated as true and
will never return a row.

Neither will variable <> null.

Therefore, if you want to know if a column is (or is not) null you must
use the 'is null' (or 'is not null') notation.

Cheers

Richard
Ben wrote:
> 
> Any one know if this is a new 9i feature or not?
> 
> I have the following table.
> 
> SQL> desc table1
>  Name                                                  Null?    Type
>  ----------------------------------------------------- --------
> -----------
>  ID
> VARCHAR2(5)
>  NAME
> VARCHAR2(5)
> 
> SQL> insert into table1 values ('242','');
> 
> 1 row created.
> 
> SQL> select * from table1;
> 
> ID    NAME
> ----- ------------------------------
> 1     A
> 2     A
> 3     C
> 4     D
> 5.    E
> aaa   C
> 242
> 
> 7 rows selected.
> 
> SQL> select * from table1 where name is null;
> 
> ID    NAME
> ----- ------------------------------
> 242
> 
> SQL> select * from table1 where name is not null;
> 
> ID    NAME
> ----- ------------------------------
> 1     A
> 2     A
> 3     C
> 4     D
> 5.    E
> aaa   C
> 
> 6 rows selected.
> 
> SQL> select * from table1 where name <> '';
> 
> no rows selected
> 
> SQL> select * from table1 where name = '';
> 
> no rows selected
> 
> My question is why do I get no rows selected for "select * from table1
> where name <> '';" and "select * from table1 where name = '';"?
> 
> Any idea?
> 
> Thanks!!
> Ben
--------------1394B3AADE645B63362978E9
Content-Type: text/x-vcard; charset=UTF-8;
 name="Richard.Foote.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Richard Foote
Content-Disposition: attachment;
 filename="Richard.Foote.vcf"

begin:vcard 
n:;Richard
x-mozilla-html:FALSE
adr:;;;;;;
version:2.1
email;internet:Richard.Foote@oracle.com
fn:Richard Foote
end:vcard

--------------1394B3AADE645B63362978E9--