Re: in(NULL)

jose <jose(at)sferacarta(dot)com> writes:
> I think I found a bug using the IN operator.
> select * from t where b not in (11,22,NULL);

This is not a bug. The behavior of NOT IN with NULLs is not very
intuitive but it is correct according to the SQL standard. The result
can only be FALSE (when b is 11 or 22, IN is definitely TRUE, so NOT IN
is definitely FALSE) or NULL (for anything else, the result is UNKNOWN).
Either way, this query will select no rows.

> I tried the same quesry in mysql and it give me
> a different result. Who are right?

If mysql gets this wrong, then they are not implementing NULLs per SQL
spec.

regards, tom lane