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 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


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


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.

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