Re: WIP: patch to create explicit support for semi and anti joins

On Aug 13, 2008, at 20:12, Tom Lane wrote:

>> Wow. That sound awesome, Tom. Stupid question: Do these join types
>> have some sort of correspondence to the SQL standard?
>
> Semi and anti joins are pretty standard concepts in relational theory,
> but they have no direct mapping in the SQL join syntax. You can write
> them with certain well-known locutions, though:
> IN and EXISTS, with certain restrictions, represent semi join
> NOT EXISTS, with certain restrictions, represents anti join
> LEFT JOIN with an "incompatible" higher IS NULL test represents
> anti join
>
> Basically what this patch is about is teaching the planner that these
> constructs are best understood via the relational-theory concepts.
> We'd been doing it in a pretty ad-hoc way before, and run into a lot
> of problems that we've had to kluge around. I think that this
> approach
> provides a structure that will actually work well.

Great. Thanks for the explanation, Tom, as always.

>> Or is this just something that's under the
>> hood an not actually a change to the syntax of SQL joins?
>
> Right, there's no "user visible" feature or syntax change here. We're
> just trying to provide better performance for certain common SQL
> idioms.

Good, it makes a lot of sense.

>
>>> What's not done:
>>>
>>> nodeMergejoin.c doesn't yet handle JOIN_ANTI. (This is just a SMOP,
>
>> I guess that means you plan to do it once there has been significant
>> testing with nestloop and hash and when the selectivity stuff is
>> done?
>
> Actually, I got it done an hour or so ago --- it turned out to be
> easier
> than I thought. It just didn't seem like part of the critical path
> for
> the patch, so I'd been willing to let it go till later.

I love it when things work that way. :-)

Best,

David