Re: in(NULL)

Jose,

First, do not waste your time comparing PG to MySql: MySql IS NOT SQL!
Second, any operation on NULL is NULL.
Third, no set can include NULL.

You want to rewrite your query as:
select * from t where b in (11,22) or b is null;

jose wrote:
>
> Hi all,
>
> I think I found a bug using the IN operator.
>
> I have a table t populated as follow:
> a | b
> ---+----
> 1 | 11
> 2 | 22
> 3 | 33
> 4 | NULL
> (4 rows)
> select * from t where b in (11,22,NULL);
> a | b
> ---+----
> 1 | 11
> 2 | 22
> (2 rows)
> select * from t where b not in (11,22,NULL);
> a | b
> ---+----
> (0 rows)
> -----------------------------------------------
> I tried the same quesry in mysql and it give me
> a different result. Who are right?
> select * from t where b not in (11,22,NULL);
> +---+---+
> | a | b |
> +---+---+
> | 3 | 33|
> +---+---+
> 1 row in set (0.00 sec)
>
> please reply to jose(at)sferacarta(dot)com
> jose
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org