Difference between IN and JOIN

From: Markus Bertheau <twanger(at)bluetwanger(dot)de>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Difference between IN and JOIN
Date: 2004-10-04 08:39:50
Message-ID: 1096879190.2651.8.camel@dicaprio.akademie1.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)bluetwanger(dot)de>

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Dag Gullberg 2004-10-04 11:35:35 Re: Concurrency problem
Previous Message Bruno Wolff III 2004-10-03 23:42:43 Re: How to convert 3 colums to timestamp with timezone