On Wed, Feb 8, 2017 at 10:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Fetter <david@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.
