Re: SQL 'in' vs join.

> 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