Thread

Re: [HACKERS] Subselects and NOTs

Yurik V. Nazaroff <yurikn@glas.apc.org> — 1998-02-21T22:31:48Z

Vadim B. Mikheev <vadim@sable.krasnoyarsk.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