On Saturday, March 12, 2016, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Don't the semantics of a SEMI JOIN also state that the output columns only
> come from the outer relation? i.e., the inner relation doesn't contribute
> either rows or columns to the final result? Or is that simply
> an implementation artifact of the fact that the only current way to perform
> a semi-join explicitly is via exists/in?
I think it's an artifact. What nodes.h actually says about it is you get
the values of one randomly-selected matching inner row, which seems like
a fine definition for the purposes we plan to put it to.
But is it a definition that actually materializes anywhere presently?
I'm not sure what we consider an authoritative source but relational algebra does define the results of semi and anti joins as only containing rows from main relation.
Given that this is largely internals (aside from the plan explanations themselves) I guess we can punt for now but calling an inner or outer join a semijoin in this case relies on a non-standard definition of semijoin - namely that it is an optimized variation of the other joins instead of a join type in its own right. This is complicated further in that we do implement a true semijoin (using exists) while we allow for an anti join to be non-standard if expressed using "left join ... is null" instead of via "not exists".
Calling these optimizations outer/inner+semi/anti preserves the ability to distinguish these versions from the standard definitions. I do like ithe idea of it being exposed and encapsulated as a distinct join type instead of being an attribute.
David J.