Re: NULL value comparison

On 08/22/2012 09:37 PM, David Johnston wrote:
> On Aug 22, 2012, at 9:23, Michael Sacket <msacket(at)gammastream(dot)com> wrote:
>
>> Good Day,
>>
>> I'm trying to figure out why a postgresql query doesn't return what I'd expect with a query like this where there are NULL values:
>>
>> select * from users where is_enabled<>'Y';
>>
>> I'm expecting it to return all records where is_enabled is 'N' or NULL. Perhaps my expectations are misguided. Any thoughts would be appreciated.
>
> The only record known to be not equal to "Y" is "N" since it is possible the unknown value represented by NULL could be "Y". If you really want both you need to use IS DISTINCT FROM

http://sqlblog.com/blogs/paul_nielsen/archive/2007/11/11/the-real-problem-with-null.aspx

Teaching that NULL means "unknown" tends to lead to confusion down the
track, in cases where NULL means "no value" or "bork bork oogabooga"
instead.

Null is interpreted as "the known value 'no value'" by aggregate
functions; were that not the case, the result of:

regress=# SELECT SUM(i) FROM ( VALUES (1),(2),(NULL),(3) ) x(i);
sum
-----
6
(1 row)

would be NULL, not 6, and the result of:

regress=# SELECT SUM(i) FROM generate_series(1,0) i;
sum
-----

(1 row)

ie a sum on no values would not make sense; it's "no value" here not
"unknown".

Null isn't consistent in meaning, and trying to treat it as "unknown"
just leads to confusion. It'd be nice if SQL had separate "UNKNOWN" and
"NO_VALUE_OR_NA" keywords instead of "NULL", but alas, it doesn't - and
I'm not sure that'd cover all the cases either.

--
Craig Ringer