Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dIZwo-0007wi-Ah for pgsql-sql@arkaria.postgresql.org; Wed, 07 Jun 2017 12:20:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dIZwn-00034u-NW for pgsql-sql@arkaria.postgresql.org; Wed, 07 Jun 2017 12:20:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dIZwm-00031Q-GA for pgsql-sql@postgresql.org; Wed, 07 Jun 2017 12:20:24 +0000 Received: from sender-of-o51.zoho.com ([135.84.80.216]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dIZwj-0002sf-Ak for pgsql-sql@postgresql.org; Wed, 07 Jun 2017 12:20:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1496838014; s=zoho; d=lightpear.com; i=jonathan-lists@lightpear.com; h=Date:From:To:Message-Id:In-Reply-To:Subject:MIME-Version:Content-Type; l=5003; bh=4d/jbyhL2tGVhaooYDLdmrkd00YBwRvdpIlioPIUzvc=; b=b4ej3/ktr3JYk0L0DXT7VsO9JLPYQlEaiu77HO8dgr78LTpgArLj5rleKjL+tUqi xsYWJWEiyqnJBZC9DEG2tN8Ix7xK5HGDTX02T+2CTqve7fyLRi2MCXWMkIAlBEujuCK grJeW2YuEDPyNzznJc8lLbtST4DrigQEuP88vhTo= Received: from mail.zoho.com by mx.zohomail.com with SMTP id 1496838013962295.6000676065079; Wed, 7 Jun 2017 05:20:13 -0700 (PDT) Date: Wed, 07 Jun 2017 13:20:13 +0100 From: Jonathan Moules To: Message-Id: <15c827e9305.ae02346553193.1322341002046903374@lightpear.com> In-Reply-To: Subject: Not getting the expected results for a simple where not in MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_127037_333604535.1496837952263" X-Priority: Medium User-Agent: Zoho Mail X-Mailer: Zoho Mail X-Pg-Spam-Score: -3.0 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_127037_333604535.1496837952263 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 7bit Hi List, I'm a little confused by what seems like it should be a simple query and was hoping someone could explain what's going on. Using PG 9.4.x CREATE TABLE aaa.testing_nulls ( str character varying(10), status character varying(2) ) Data: "first";"aa" "second";"aa" "third";null "fourth";"bb" null;"aa" null;"bb" If I run: select str from aaa.testing_nulls where status in ('aa') Against the table, I get the expected result: "first" "second" null But I want to get the items that don't have a value of 'aa'. Obviously in this case I can simply add "not" to the "where status in" but that's not suitable for my actual use-case (which is where this problem came to light). Instead, I'm nesting the original as a subquery: select * from aaa.testing_nulls where str not in ( select str from aaa.testing_nulls where status in ('aa') ) Conceptually to me at least, this should work. I expect to get the values: "third" "fourth" But instead when I run it I get 0 results. It seems to relate to the nulls. If I change the above and add "and str is not null" into the subquery: select * from aaa.testing_nulls where str not in ( select str from aaa.testing_nulls where status in ('aa') and str is not null ) It now gives the expected results. Why is this? (I tested this in SQLite too, and get the same behaviour, so I guess it's a generic SQL thing I've never encountered before.) Thanks, Jonathan ------=_Part_127037_333604535.1496837952263 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =
Hi List,
I'm a little confused by what seems like it shoul= d be a simple query and was hoping someone could explain what's going on.Using PG 9.4.x

CREATE TABLE aaa.testing_nulls
(
  str c= haracter varying(10),
  status character varying(2)
)

Dat= a:
"first";"aa"
"second";"aa"
"third";null
"fourth";"bb"
nul= l;"aa"
null;"bb"

If I run:
    select
 =        str
    from
 &nb= sp;      aaa.testing_nulls
     =    where
           &n= bsp;status in ('aa')
        
Against t= he table, I get the expected result:
"first"
"second"
null

= But I want to get the items that don't have a value of 'aa'. Obviously in t= his case I can simply add "not" to the "where status in" but that's not sui= table for my actual use-case (which is where this problem came to light). I= nstead, I'm nesting the original as a subquery:

select
 &nbs= p;  *
from
    aaa.testing_nulls
where
&nbs= p;   str not in
    (
    &n= bsp;   select
         &nbs= p;  str
        from
   =          aaa.testing_nulls
 &nbs= p;          where
    =             status in ('aa')    )

Conceptually to me at least, this should work= . I expect to get the values:
"third"
"fourth"
But instead when I = run it I get 0 results.

It seems to relate to the nulls. If I change= the above and add "and str is not null" into the subquery:

select    *
from
    aaa.testing_nulls
w= here
    str not in
    (
 &nb= sp;      select
       &nbs= p;    str
        from
&= nbsp;           aaa.testing_nulls            where
 &= nbsp;              status= in ('aa')
            &nbs= p;   and str is not null
    )

It now gi= ves the expected results.
Why is this?
(I tested this in SQLite too, = and get the same behaviour, so I guess it's a generic SQL thing I've never = encountered before.)
Thanks,
Jonathan
------=_Part_127037_333604535.1496837952263--