Xref: alice comp.databases.oracle.server:20284
Path: alice!news-feed.fnsi.net!ais.net!btnet-peer!btnet!dispose.news.demon.net!demon!news.demon.co.uk!demon!mailgate.celltech.co.uk!not-for-mail
From: James Petts <jpetts@celltech.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: Either it's equal or it isn't, right?
Date: Tue, 05 May 1998 17:12:44 +0100
Organization: Celltech Therapeutics Ltd
Message-ID: <354F3A7C.9015310B@celltech.co.uk>
References: <roy-0505981118410001@qwerky.med.nyu.edu>
X-Trace: news.demon.co.uk 894384745 nnrp-04:20866 NO-IDENT mailgate.celltech.co.uk:194.217.191.10
X-Complaints-To: abuse@demon.net
X-Mailer: Mozilla 4.04 [en] (WinNT; U)
MIME-Version: 1.0
To: Roy Smith <roy@popmail.med.nyu.edu>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Cache-Post-Path: firewall.celltech!unknown@jpetts.celltech
Lines: 33

The other possibility is that the record has a NULL value in the field.
Recall that NULL is never equal and never not equal to
anything. If you do a

SELECT count(*) FROM phone WHERE record_status IS NULL

I suspect you will get 13042 - 12983 = 59 returned.

Roy Smith wrote:
> 
> I'm really stumped by the following three queries.  The first says there
> are N1 records in the table.  The second says N2 of them have a given
> value.  Therefore, shouldn't the number of records which don't have the
> value be equal to N1-N2?  Either record_status is 'A' or it isn't 'A'.
> What other possibilities exist?
> 
> SQL> select count(*) from phone;
> 
>   COUNT(*)
> ----------
>      13042
> 
> SQL> select count(*) from phone where record_status = 'A';
> 
>   COUNT(*)
> ----------
>      12983
> 
> SQL> select count(*) from phone where record_status != 'A';
> 
>   COUNT(*)
> ----------
>          0