Skip site navigation (1) Skip section navigation (2)

Re: SQL 'in' vs join.

From: Don Baccus <dhogaza(at)pacifier(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: mlw <markw(at)mohawksoft(dot)com>, Hannu Krosing <hannu(at)tm(dot)ee>, Hackers List <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: SQL 'in' vs join.
Date: 2000-11-30 19:59:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
At 10:52 AM 11/30/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza(at)pacifier(dot)com> writes:
>> The optimizer should do a better job on your first query, sure, but why
>> don't you like writing joins?
>The join wouldn't give quite the same answers.  If there are multiple
>rows in table2 matching a particular table1 row, then a join would give
>multiple copies of the table1 row, whereas the WHERE foo IN (sub-select)
>way would give only one copy.  SELECT DISTINCT can't be used to fix
>this, because that would eliminate legitimate duplicates from identical
>table1 rows.

Hmmm...I was presuming that "field" was a primary key of table1, so
such duplicates wouldn't exist (and SELECT DISTINCT would weed out
duplicates from table2 if "field" isn't a primary key of table2, i.e.
if table2 has a many-to-one relationship to table1).  For many-to-many
relationships yes, you're right, the "in" version returns a different

>Now that the executor understands about multiple join rules (for
>OUTER JOIN support), I've been thinking about inventing a new join rule
>that says "at most one output row per left-hand row" --- this'd be sort
>of the opposite of the LEFT OUTER JOIN rule, "at least one output row
>per left-hand row" --- and then transforming IN (sub-select) clauses 
>that appear at the top level of WHERE into this kind of join.  Won't
>happen for 7.1, though.

Same trick could be used for some classes of queries which do a SELECT DISTINCT
on the results of a join, too ...

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at

In response to

pgsql-hackers by date

Next:From: Michael ForkDate: 2000-11-30 22:54:27
Subject: pg_trigger and tgargs
Previous:From: Mitch VincentDate: 2000-11-30 19:17:28
Subject: Re: Size of my data base?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group