At 10:52 AM 11/30/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.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@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
