Skip site navigation (1) Skip section navigation (2)

Re: in(NULL)

From: Jean-Luc Lachance <jllachan(at)nsd(dot)ca>
To: jose(at)sferacarta(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: in(NULL)
Date: 2002-05-23 16:34:50
Message-ID: 3CED1A2A.4AB01719@nsd.ca (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

  • in(NULL) at 2002-05-22 13:38:07 from jose

pgsql-bugs by date

Next:From: Michael BeckstetteDate: 2002-05-23 19:23:55
Subject: length limitation in '=' operator when comparing TEXT fields ?
Previous:From: Tom LaneDate: 2002-05-23 16:21:28
Subject: Re: Numeric Datatype

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group