I have always known about the
UNION operator in SQL, but only recently discovered that there were other set operators,
EXCEPT. I haven't been able to find an operator that does the fourth big set operator, the symmetric difference (e.g. the opposite of
It looks like I can get the desired output by using something like
SELECT Field FROM A UNION SELECT Field FROM B EXCEPT SELECT Field FROM A INTERSECT SELECT Field FROM B
(assuming I got the precedence right), or by doing an anti-full-join:
SELECT A.Field, B.Field FROM A FULL JOIN B ON B.Id = A.Id WHERE B.Id IS NULL OR A.Id IS NULL
But both of those look like rather intensive queries, especially compared to the other three basic set operations. Is there a symmetric difference operation in SQL and I just can't find it in the documentation? Or is there a "canonical" way to implement it in T-SQL?