EXISTS and IN seem to work ok for:

dynacom=# SELECT count(*) from parts p where p.id in (select md.partid
from machdefs md);
count
-------
12656
(1 row)

dynacom=# SELECT count(*) from parts p where exists (select 1 from
machdefs md where md.partid = p.id);
count
-------
12656
(1 row)

Whereas IN seems to not working ok when negated as shown below:

dynacom=# SELECT count(*) from parts p where p.id not in (select md.partid
from machdefs md);
count
-------
0
(1 row)

dynacom=# SELECT count(*) from parts p where not exists (select 1 from
machdefs md where md.partid = p.id);
count
-------
291
(1 row)

dynacom=# SELECT version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.1 on i386-unknown-freebsd5.1, compiled by GCC gcc (GCC)
3.2.2 [FreeBSD] 20030205 (release)
(1 row)


On PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.96, i get
identical results.

Is this a known issue? Addressed before?

--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Achilleus Mantzios <achill (AT) matrix (DOT) gatewaynet.com> writes:
If the sub-select returns any NULLs then this behavior is per SQL spec.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

O kyrios Tom Lane egrapse stis Feb 5, 2004 :

Thanx a lot!

--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings