From: Derick H Siddoway
Date: March 30 1999 10:45pm
Subject: Re: x NOT IN (y) <=> x != y
List-Archive: http://lists.mysql.com/mysql/1210
Message-Id: <0F3C6370154091FA*/c=us/admd=attmail/prmd=amex/o=unresolved/ou=HUB1/ou=AMEX/s=Siddoway/g=Derick/i=H/@MHS>

Hi.

I look at it like this:

NOT IN is an SQL statement, so SQL values (i.e., NULL)
are valid results.  != is more of a mathematical 
statement (straight libm.so math), where NULL isn't
a valid result.  (Don't give me any crap about New
Math.)

NOT IN looks at a set and belonging to a set.
!= looks at a mathematic equivalence, and NULLs don't
figure in math equivalences.

(New Math says that these two are completely congruent,
since "=" can be considered the same as "is in the
same set as", but that's not how math works in most
programming languages.)

-- 
derick



From:	cdhsm%cthulhu.co.uk@Internet on 03/30/99 07:34 AM
To:	mysql%lists.mysql.com@Internet
cc:	 (bcc: Derick H Siddoway/TC/TRS/American Express)
Subject:	x NOT IN (y)  <=>  x != y


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

/---------------------------------------------\
|                                             |
|      Corin Hartland-Swann - Webmaster       |
|                                             |
+---------------------------------------------+
|                                             |
|      The International Lyrics Network       |
|                                             |
|           http://www.lyrics.net/            |
|                                             |
| mysql 3.22.20 - apache 1.3.4 - perl 5.00505 |
|                                             |
\---------------------------------------------/



---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread1187@stripped

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@stripped instead.