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

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Aug 13, 2008, at 17:31, Tom Lane wrote:
>> Introduce JOIN_SEMI and JOIN_ANTI join types,

> 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.

> 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.

>> 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.

regards, tom lane