I just bumped into this:
postgres=# CREATE TABLE atable (id int4);
CREATE TABLE
postgres=# CREATE TABLE btable (id int4);
CREATE TABLE
postgres=# INSERT INTO atable VALUES (1),(2),(3);
INSERT 0 3
postgres=# INSERT INTO btable VALUES (1),(2),(3),(1);
INSERT 0 4
postgres=# SELECT * FROM atable WHERE id IN
(SELECT d.id
FROM atable d LEFT JOIN btable e
ON d.id = e.id)
;
id
----
1
1
2
3
(4 rows)
On 8.3 this returns correctly just three rows: 1 2 3.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com