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
Thread
x NOT IN (y) <=> x != yCorin Hartland-Swann30 Mar
  • x NOT IN (y) <=> x != yMichael Widenius30 Mar
  • Re: x NOT IN (y) <=> x != yMartin B. Jespersen30 Mar
Re: x NOT IN (y) <=> x != yDerick H Siddoway31 Mar
Re: x NOT IN (y) <=> x != yMichael Widenius31 Mar