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 C3A8132A3B2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  4 Oct 2004 09:40:14 +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 18274-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  4 Oct 2004 08:40:04 +0000 (GMT)
Received: from web11.manitu.net (web11.manitu.net [217.11.48.111])
	by svr1.postgresql.org (Postfix) with ESMTP id 4A74132A3A9
	for <pgsql-sql@postgresql.org>; Mon,  4 Oct 2004 09:40:01 +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 i948dsC32194
	for <pgsql-sql@postgresql.org>; Mon, 4 Oct 2004 10:39:54 +0200
Subject: Difference between IN and JOIN
From: Markus Bertheau <twanger@bluetwanger.de>
To: pgsql-sql <pgsql-sql@postgresql.org>
Content-Type: text/plain
Message-Id: <1096879190.2651.8.camel@dicaprio.akademie1.de>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Mon, 04 Oct 2004 10:39:50 +0200
Content-Transfer-Encoding: 7bit
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/18
X-Sequence-Number: 19121

Hi,

I lately wondered if there is a difference between a JOIN and a IN in
queries similar to the following:

SELECT f1 FROM t1 JOIN t2 ON (t.f2 = t2.f2) WHERE t2.f3 = x

SELECT f1 FROM t1 WHERE t1.f2 IN (SELECT f2 FROM t2 WHERE f3 = x)

As I see it there's no semantic difference between the two. Are there
differences in performance or anything else that matters? The execution
plans seem to match except for the use of an "Hash IN Join" in place of
a "Hash Join". Estimated costs match.

Thanks for your advice

-- 
Markus Bertheau <twanger@bluetwanger.de>