9

I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and 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 INTERSECT.)

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?

  • 2
    (a EXCEPT b) UNION ALL (b EXCEPT a); might be more efficient. – ypercubeᵀᴹ Jul 15 '14 at 15:23
  • @ypercube that does 3 joins or join-like operations. I cannot think of any reason this might be more efficient than a full join. – usr May 23 '15 at 11:38
  • @usr it's actually 2 join-like operations, not 3. Union All is much less costly operation. I agree that FULL JOIN may be more efficient. Testing can reveal which is best. And of course, if more/different columns from each table are needed, my solution is not easily expandable. – ypercubeᵀᴹ May 23 '15 at 13:45
2

All set operators are translated to joins or join-like operators. You can witness that in the query plan.

For that reason the full outer join that you have there is the most efficient you can do. Disregarding, of course, the hopefully rare situation in which the optimizer picks a bad plan and a rewrite happens to perform better by luck. This can always happen.

  • That makes sense, however, the reason I like to use the set operators is because they don't create "extra columns"... I can do things like SELECT Id FROM A WHERE <stuff> EXCEPT Select Id FROM A WHERE <other stuff> and get a single list of Id. I can't figure out how to do that with a full join... do I just need to deal with having two sets of Id columns and union them back together myself? – KutuluMike Jul 15 '14 at 18:16
  • You can say ISNULL(a.Col, b.Col) AS Col. Wrap that in a derived table or CTE and you can use the "folded" result set for further operation on it. (Btw, I agree the symmetric difference operator should exist.) – usr Jul 15 '14 at 18:20
  • 2
    @MichaelEdenfield On the other hand, what about when you want other columns that aren't being used in the compare/distinct? You can do that with a join but not with intersect/except. So I could see in some cases that variation actually ending up becoming much more complex in the long run. – Aaron Bertrand Jul 15 '14 at 19:11
2

I think this is a pretty good solution. It uses a union all between two selects with Not Exists sub-queries. Better than combining Union and Except because you can include fields that don't match in the projection.

SELECT  'A' TableName, FileType FROM KFX_Inventory I 
    WHERE Not Exists (Select Top 1 1 from KFX_FileType FT WHERE FT.FileType = I.FileType)
UNION ALL
SELECT  'B', FileType FROM KFX_FILEType FT 
    WHERE Not Exists (Select Top 1 1 from KFX_Inventory I WHERE FT.FileType = I.FileType)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.