Re: [HACKERS] Subselects and NOTs

Vadim B. Mikheev <vadim(at)sable(dot)krasnoyarsk(dot)su> wrote:

> create table a (a int, a1 char(8));
> create table b (b int);
> insert into a values (1, 'one');
> insert into a values (NULL, 'null');

> insert into b values (1);
> insert into b values (NULL);

> select * from a where a in (select * from b);
> -- 1 row with a == 1 expected
> select * from a where a not in (select * from b);
> -- 0 row expected
> select * from a where not a in (select * from b);
> -- 0 row in Oracle & Informix, 1 row in Pg (with a == NULL), SyBase ???

Should be 0 row(s) expected. Here's why: three-value logic.

a in (select...)
a == any (select...)

(a=1) == (b==1) is 'true'
(a=1) == (b==NULL) is 'unknown'
'true' OR 'unknown' is 'true'.

(a=NULL) == (b==1) is 'unknown'
(a=NULL) == (b==NULL) is 'unknown'
'unknown' OR 'unknown' is 'unknown'
not ('unknown') is 'unknown'
shouldn't be in 'where not a in...' query

Hope this helps.

--
Yurik