Re: select, where and null-values (or: select null<>'1' is fuzzy)

Peter Pilsl <pilsl@goldfisch.at> writes:

> I've a problem when selecting values out a table.
>
> manana=# select * from test;
>  l1 | l2 | l3
> ----+----+----
>  1  | 2  | 3
>  2  | 3  | 4
>  3  | 4  | 5
>  4  | 5  | 6
>     | 5  | 6
> (5 rows)
>
> where l1 is NULL in the last line.
> now I do
>
> manana=# select * from test where l1<>1;
>  l1 | l2 | l3
> ----+----+----
>  2  | 3  | 4
>  3  | 4  | 5
>  4  | 5  | 6
> (3 rows)
> and dont get the line with the null-entry !

This has been discussed a lot recently.  Basically NULL means
"unknown"; so you can't say it's equal or unequal to any non-NULL
value.  The above behavior is in accordance with the SQL standard.

If you tell us what you are trying to do (rather than giving us a toy
example) perhaps we can make some suggestions.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan