Thread

Re: [HACKERS] Subselects and NOTs

Mattias Kregert <matti@algonet.se> — 1998-02-23T17:11:50Z

ocie@paracel.com wrote:
>
> On another note, I have been following this "not a in b" vs "not a in
> b" discussion and it seems to me that the two statements are logically
> equivalent.  Testing for a's membership in the set b and then negating
> should be equivalent to testing for a's membership in the compliment
> of set b.  In these tests, nulls seem to be treated just like any
> other value.
> 
> Ocie

According to the SQL standard: Where 'NOT' and 'IN' are written next to
each other, this is an alias for "<>ALL", and 'IN' is an alias for
"=ANY". Therefore:

"a NOT IN b" evaluates as: (a) <>ALL (b)
"NOT a IN b" evaluates as: NOT ( (a) =ANY (b) )

...which give these results:

 NOT 1 IN 2	true
 1 NOT IN 2	true

 NOT 1 IN NULL	true [NOT (1 =ANY NULL)]
 1 NOT IN NULL	false [1 <>ALL NULL]

Using "NOT IN" is a little confusing, since you might not think about
the two words as only one operator, which cannot be split in two.

/* m */