"David E. Wheeler" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers