weird exists behaviour

From: Daniel Lopez <ridruejo(at)atm9(dot)com(dot)dtu(dot)dk>
To: pgsql-sql(at)hub(dot)org
Subject: weird exists behaviour
Date: 1999-09-23 01:33:52
Message-ID: 199909230133.DAA16345@atm9.com.dtu.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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!)

Browse pgsql-sql by date

  From Date Subject
Next Message Roberto Fichera 1999-09-23 11:11:14 Query performances !?
Previous Message Hutton, Rob 1999-09-22 19:20:09 RE: [SQL] Re: [INTERFACES] JDBC and getting just assigned serial number