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.
