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