Re: SQL 'in' vs join.

> 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.
>
> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026