pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifference between IN and JOIN
3+ messages / 2 participants
[nested] [flat]
* Difference between IN and JOIN
@ 2004-10-04 08:39 Markus Bertheau <twanger@bluetwanger.de>
2004-10-04 14:17 ` Re: Difference between IN and JOIN Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Markus Bertheau @ 2004-10-04 08:39 UTC (permalink / raw)
To: 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@bluetwanger.de>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Difference between IN and JOIN
2004-10-04 08:39 Difference between IN and JOIN Markus Bertheau <twanger@bluetwanger.de>
@ 2004-10-04 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-04 14:23 ` Re: Difference between IN and JOIN Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-10-04 14:17 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
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:
> 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.
There's plenty of difference, if t2 contains repeated occurrences of f2
values.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Difference between IN and JOIN
2004-10-04 08:39 Difference between IN and JOIN Markus Bertheau <twanger@bluetwanger.de>
2004-10-04 14:17 ` Re: Difference between IN and JOIN Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-10-04 14:23 ` Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 0 replies; 3+ messages in thread
From: Markus Bertheau @ 2004-10-04 14:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
В Пнд, 04.10.2004, в 16:17, Tom Lane пишет:
> 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:
>
> > 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.
>
> 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
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-10-04 14:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-04 08:39 Difference between IN and JOIN Markus Bertheau <twanger@bluetwanger.de>
2004-10-04 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-04 14:23 ` Markus Bertheau <twanger@bluetwanger.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox