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