From: Michael Widenius Date: March 31 1999 1:14am Subject: Re: x NOT IN (y) <=> x != y List-Archive: http://lists.mysql.com/mysql/1218 Message-Id: <14081.30097.927637.947432@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit >>>>> "Derick" == Derick H Siddoway writes: Derick> Hi. Derick> I look at it like this: Derick> NOT IN is an SQL statement, so SQL values (i.e., NULL) Derick> are valid results. != is more of a mathematical Derick> statement (straight libm.so math), where NULL isn't Derick> a valid result. (Don't give me any crap about New Derick> Math.) Derick> NOT IN looks at a set and belonging to a set. Derick> != looks at a mathematic equivalence, and NULLs don't Derick> figure in math equivalences. Derick> (New Math says that these two are completely congruent, Derick> since "=" can be considered the same as "is in the Derick> same set as", but that's not how math works in most Derick> programming languages.) Derick> -- Derick> derick Hi! I just checked the SQL standard; The statement: x IN (x1,x2,x3...) is defined to be eqvialent with: x=x1 OR x=x2 OR x=x3 and x NOT IN (x1,x2,x3...) is eqvialent with: NOT (x IN (x1,x2,x3...)) This means that x1, x2, x3... can't be NULL values (or at least that even if x is NULL, then it will not match any NULL in x1, x2, x3...)! Note that no one has claimed the SQL and NULL has anything to do with New Math. Regards, Monty