Re: where clause
- From: Jonathan Gennick <jonathan@xxxxxxxxxxx>
- To: "David Boyd" <davidb158@xxxxxxxxxxx>
- Date: Fri, 6 Feb 2004 15:26:55 -0500
DB> I found following results that I don't understand.
Ok.
SQL>> SELECT COUNT(*) FROM test;
This counts rows. Nullity isn't an issue, since a "row"
cannot be null. Only columns may be null. You may know
that already.
SQL>> SELECT COUNT(*) FROM test WHERE status_flag !='A';
DB> COUNT(*)
DB> ----------
DB> 38
DB> I thought the last query should return 1676 (status_flag ='S' + status_flag
DB> is null) instead of 38 (status_flag ='S' only). Any ideas???
The above query will not count NULL status_flag values.
That's because NULL is not the same as !='A'.
The thing I like to show people is to issue a query like the
following:
SELECT *
FROM test
WHERE status_flag = 'A'
OR status_flag != 'A';
At first glance, you might think this query would return
*all* rows in the table. However, it won't. Rows with NULL
status_flag values will not satisfy either condition. It's a
bit counterintuitive. That's the joy of working with
three-valued logic.
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@xxxxxxxxxxx
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to Oracle-article-request@xxxxxxxxxxx and
include the word "subscribe" in either the subject or body.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
