Re: Optimization rules for semi and anti joins

On 11 Feb 2009, at 00:03, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Actually, that makes less sense than the antijoin case. For antijoin
> there is a well-defined value for the extended columns, ie null. For
> a semijoin the RHS values might come from any of the rows that happen
> to join to the current LHS row, so I'm just as happy that it's
> syntactically impossible to reference them.

Actually I think the way mysql users used to spell EXISTS/IN before
mysql supported them would qualify as a semijoin where you can access
the columns:

SELECT distinct a.* from a,b WHERE a.id = b.id

To access columns from b in postgres you would have to use DISTINCT ON.