| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: where clause
David,
Welcome to the world of 3-valued logic. Please check your logical thoughts at the door.
The problem is that NULL does not evaluate to TRUE with the test of != A. NULL can be thought of as unknown data (not spaces, not zeroes, not the absence of data). In order to pass the non-equality test, the condition must evaluate to TRUE (the other options are FALSE or NULL (NOT TRUE/NOT FALSE)). Remember that NOT TRUE does not mean FALSE and NOT FALSE does not mean TRUE.
Let's see how this analogy works. Think of the table as a table with a bunch of status_flags laying on it, each one separately wrapped (not for individual sale). Some of the wrappers are clear, while others are opaque. When you are asked to count the 'S' status_flags, you can look through the clear wrappers and count 38 of them. When you look at the opaque wrappers, you can't tell if the status_flag is 'A', 'S' or some other value, so you can't say that it is an 'S' nor can you say that it is NOT an 'S', you simply don't know. When you are asked to count the ones that are NOT 'A', as you examine each wrapper, you have to count only those status_flags that you KNOW are not 'A'. Again, 'S' is not 'A', so you count each of those. For the opaque (NULL) status_flags, you don't know that they are NOT 'A', so you can't count them as you don't know that the statement is TRUE. If the question were, "How many status_flags are not 'A' or are unknown?", you could then count the opaque (NULL) status_flags.
Daniel Fink
David Boyd wrote:
> Hi List,
>
> I found following results that I don't understand.
>
> SQL> desc test
> Name Null? Type
> ----------------------------------------- -------- -----------------
> STATUS_FLAG CHAR(1)
>
> SQL> SELECT COUNT(*) FROM test;
>
> COUNT(*)
> ----------
> 1681
>
> SQL> SELECT COUNT(*) FROM test WHERE status_flag IS NULL;
>
> COUNT(*)
> ----------
> 1638
>
> SQL> SELECT COUNT(*) FROM test WHERE status_flag ='A';
>
> COUNT(*)
> ----------
> 5
>
> SQL> SELECT COUNT(*) FROM test WHERE status_flag ='S';
>
> COUNT(*)
> ----------
> 38
>
> SQL> SELECT COUNT(*) FROM test WHERE status_flag !='A';
>
> COUNT(*)
> ----------
> 38
>
> I thought the last query should return 1676 (status_flag ='S' + status_flag
> is null) instead of 38 (status_flag ='S' only). Any ideas???
>
> _________________________________________________________________
> Let the advanced features & services of MSN Internet Software maximize your
> online time. http://click.atdmt.com/AVE/go/onm00200363ave/direct/01/
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Feb 06 2004 - 14:15:23 CST
 |
 |