> 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.
>
> 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.
Of course, we will have the query tree redesign for 7.2, right, make
that unnecessary.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026