Re: Idea on how to simplify comparing two sets

On Wed, Feb 8, 2017 at 10:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Fetter <david(at)fetter(dot)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.