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