| 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: | Whole Thread | Raw Message | 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 |