Re: [HACKERS] Subselects and NOTs

ocie(at)paracel(dot)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 */