Received: from atm9.com.dtu.dk (atm9.com.dtu.dk [192.38.77.129]) by hub.org (8.9.3/8.9.3) with ESMTP id VAA87256 for ; Wed, 22 Sep 1999 21:21:52 -0400 (EDT) (envelope-from ridruejo@atm9.com.dtu.dk) Received: (from ridruejo@localhost) by atm9.com.dtu.dk (8.8.7/8.8.7) id DAA16345 for pgsql-sql@hub.org; Thu, 23 Sep 1999 03:33:52 +0200 Message-Id: <199909230133.DAA16345@atm9.com.dtu.dk> Subject: weird exists behaviour To: pgsql-sql@hub.org Date: Thu, 23 Sep 1999 01:33:52 +0000 () From: Daniel Lopez X-Mailer: ELM [version 2.4ME+ PL32 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit 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 -- 1 2 (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!)