On Wed, Feb 8, 2017 at 10:30 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> David Fetter <da...@fetter.org> writes: > > On Wed, Feb 08, 2017 at 11:22:56AM -0500, Tom Lane wrote: > >> Yes. I think a new set-operation keyword would inevitably have to > >> be fully reserved --- UNION, INTERSECT, and EXCEPT all are --- which > >> means that you'd break every application that has used that word as > >> a table, column, or function name. > > > I've long wanted a SYMMETRIC DIFFERENCE join type, that being the only > > elementary set operation not included in join types, but nobody at the > > SQL standards committee seems to have cared enough to help. > > I wonder whether you could shoehorn it in with no new reserved word > by spelling it "EXCEPT SYMMETRIC", which could be justified by the > precedent of BETWEEN SYMMETRIC. But not sure what to do with > duplicate rows (ie, if LHS has two occurrences of row X and RHS > has one occurrence, do you output X?) > Without SYMMETRIC its defined to return: max(m-n,0) with SYMMETRIC I'd think that would just change to: abs(m-n) Then you still have to apply ALL or DISTINCT on the above result. David J.