Re: SQL 'in' vs join.

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
result.

>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
http://donb.photo.net.