List: General Discussion « Previous MessageNext Message » From: Michael Widenius Date: March 31 1999 1:14am Subject: Re: x NOT IN (y) <=> x != y View as plain text
>>>>> "Derick" == Derick H Siddoway <Derick.H.Siddoway@stripped>
> 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