List:General Discussion« Previous MessageNext Message »
From:Martin B. Jespersen Date:March 30 1999 2:49pm
Subject:Re: x NOT IN (y) <=> x != y
View as plain text  
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
Thread
x NOT IN (y) <=> x != yCorin Hartland-Swann30 Mar
  • x NOT IN (y) <=> x != yMichael Widenius30 Mar
  • Re: x NOT IN (y) <=> x != yMartin B. Jespersen30 Mar
Re: x NOT IN (y) <=> x != yDerick H Siddoway31 Mar
Re: x NOT IN (y) <=> x != yMichael Widenius31 Mar