Re: Optimization rules for semi and anti joins

On Tue, Feb 10, 2009 at 5:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I don't understand why antijoins need to null-extend the tuple at all.
>
> Well, we are talking theoretical definition here, not implementation.
> But if you need an example where the column values can be referenced:
>
> select * from a left join b on (a.id = b.id)
> where b.id is null
>
> 8.4 does recognize this as an antijoin, if the join operator is strict.

Oh, I see. Hmm.

>> In the case of a semijoin, it's theoretically possible that there
>> could be syntax which allows access to the attributes of the outer
>> side of the relation, though IN and EXISTS do not.
>
> 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.

You might some day want to optimize this case as a semijoin, or
something similar to a semijoin:

SELECT foo.a, (SELECT bar.b FROM bar WHERE bar.a = foo.a) FROM foo;

...Robert