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.