Can someone explain this behaviour (bug? feature?) of EXISTS.
(also, is EXISTS documented anywhere in the postgres
documentation?)
db=> create table t1 (f1 int4);
CREATE
db=> create table t2 (f2 int4);
CREATE
db=> insert into t1 values (1)
db-> ;
INSERT 6008882 1
db=> insert into t1 values (2);
INSERT 6008883 1
db=> insert into t2 values (1);
INSERT 6008884 1
db=> select * from t1;
f1
--12(2 rows)
db=> select * from t2;
f2
--1
(1 row)
db=> select * from t1 where f1 not in (select * from t2);
f1
--
2
(1 row)
So far so good
db=> select f1 from t1 where not exists (select * from t2 where t1.f1=t2.f2);
f1
--
2
(1 row)
So far so good
db=> select * from t1 where not exists (select * from t1 where
t1.f1=t2.f2);
f1
--
(0 rows)
(why???? )
db=> select * from t1 where not exists (select * from t1 as t4 where
t1.f1=t2.f2);
f1
--
2
(1 row)
(and surprisingly this works!)