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