Re: Sub-query having NULL row returning FALSE result

Hi

The actual statement is MERGE INTO <table> NOT MATCHED, which in PG
migrated to WITH - INSERT

however, yes, the SQL-statement in previous does not work in other
databases too, I was wrong

Thanks, thanks again
Sridhar
OpenText

On Wed, Jun 29, 2016 at 11:58 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>