Corin Hartland-Swann wrote:
>
> Hi there,
>
> While trying to get my head around NULL values, I stumbled on the
> following:
>
> CREATE TABLE foo (bar int);
>
> INSERT INTO foo VALUES (NULL),(1),(2);
>
> SELECT * FROM foo WHERE bar NOT IN (1);
>
> returns NULL and 2
>
> SELECT * FROM foo WHERE bar != 1;
>
> returns 2
>
> NULL is supposed to mean "unknown value", so surely there is no way of
> knowing whether the row where bar = NULL is 1 or not 1.
>
> I'd always worked under the assumption that
>
> x NOT IN (y) <=> x != y
>
> Can anyone explain this?
>
> Thanks in advance,
>
> Corin
>
Hmm aren't you supposed to use <> and not != in SQL?
Anyways, One explanation might be that you can't use logical operators on NULL values.
That is the reason why you don't do = NULL but IS NULL.
--
Martin B. Jespersen
Technical Project Leader
Icon Medialab A/S (http://www.IconMedialab.dk)
E-Mail: Martin@stripped
Mail: Havnegade 39, DK-1058 Copenhagen K
Voice: (+45) 7023-1001
Fax: (+45) 33320209
