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.

regards, tom lane