X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 37A7E329EF1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  4 Oct 2004 15:24:02 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 26196-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  4 Oct 2004 14:23:56 +0000 (GMT)
Received: from web11.manitu.net (web11.manitu.net [217.11.48.111])
	by svr1.postgresql.org (Postfix) with ESMTP id 7A2DB329E93
	for <pgsql-sql@postgresql.org>; Mon,  4 Oct 2004 15:23:58 +0100 (BST)
Received: from [62.165.4.166] (dicaprio.akademie1.de [62.165.4.166])
	(authenticated)
	by web11.manitu.net (8.10.2-SOL3/8.10.2) with ESMTP id i94ENtC32339;
	Mon, 4 Oct 2004 16:23:55 +0200
Subject: Re: Difference between IN and JOIN
From: Markus Bertheau <twanger@bluetwanger.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
In-Reply-To: <20688.1096899436@sss.pgh.pa.us>
References: <1096879190.2651.8.camel@dicaprio.akademie1.de>
	<20688.1096899436@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Message-Id: <1096899829.2651.13.camel@dicaprio.akademie1.de>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Mon, 04 Oct 2004 16:23:49 +0200
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200410/22
X-Sequence-Number: 19125

=D0=92 =D0=9F=D0=BD=D0=B4, 04.10.2004, =D0=B2 16:17, Tom Lane =D0=BF=D0=B8=
=D1=88=D0=B5=D1=82:
> Markus Bertheau <twanger@bluetwanger.de> writes:
> > I lately wondered if there is a difference between a JOIN and a IN in
> > queries similar to the following:
>=20
> > SELECT f1 FROM t1 JOIN t2 ON (t.f2 =3D t2.f2) WHERE t2.f3 =3D x
>=20
> > SELECT f1 FROM t1 WHERE t1.f2 IN (SELECT f2 FROM t2 WHERE f3 =3D x)
>=20
> > As I see it there's no semantic difference between the two.
>=20
> There's plenty of difference, if t2 contains repeated occurrences of f2
> values.

In my case f2 is the primary key of t2. I probably didn't state the
point of my question well enough: You can write an IN query as a
semantically equal JOIN. I wondered, which one I should prefer, and on
which grounds.

Thanks

--=20
Markus Bertheau <twanger@bluetwanger.de>