From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Sridhar N Bamandlapally <sridhar(dot)bn1(at)gmail(dot)com> |
Cc: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Sub-query having NULL row returning FALSE result |
Date: | 2016-06-29 06:28:03 |
Message-ID: | 25435.1467181683@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Sridhar N Bamandlapally <sridhar(dot)bn1(at)gmail(dot)com> writes:
> postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);
> postgres=# INSERT INTO emp VALUES (null, 'aaa');
> ...
> postgres=# INSERT INTO emp SELECT * FROM (SELECT 5::integer id,
> 'eee'::varchar ename) nr WHERE id NOT IN (SELECT id FROM emp);
> INSERT 0 0
This is expected. NOT IN can never succeed if there are any nulls
returned by the sub-select, because the nulls represent "unknown",
and so it's unknown whether there is a match to the outer "id"
value, and WHERE takes a null (unknown) result as false not true.
Certainly there are things to quibble with in that behavior, but
it's what's been required by the SQL standard since 1992.
> but this is working with other databases
Really? None that are compliant with the SQL standard, for sure.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | mohd abdul azeem | 2016-06-29 07:36:12 | Enquiry |
Previous Message | Sridhar N Bamandlapally | 2016-06-29 06:07:26 | Sub-query having NULL row returning FALSE result |