Does the SQL spec provide for a better way to do a exclusive ORing of two sets?

Question

I have a result set A which is 10 rows 1-10 {1,2,3,4,5,6,7,8,9,10}, and B which is 10 rows consisting of evens 1-20 {2,4,6,8,10,12,14,16,18,20}. I want to find of the elements that are in one set but not both. There are no other columns in the rows.

I know a UNION will be A + B. I can find the ones in both A and B with A INTERSECT B. I can find all of the rows in A that are not in B with A EXCEPT B.

This brings me to the question how do I find all rows that are in A or B, but not both, is there a transitive equiv of ( A EXCEPT B ) UNION ( B EXCEPT A) in the sql spec? I'm wanting a set of {1,3,5,7,9,12,14,16,18,20}. I believe this can also be written A UNION B EXCEPT ( A INTERSECT B )

Is there a mathy reason in set theory why this can't be done in one operation (that can be explained to someone who doesn't understand set theory)? Or, is it just not implemented because it is so simple to build yourself? Or, do I just not know of a better way to do it?

I'm thinking this must be in the SQL spec somewhere: I know the thing is humongous.

Answer 1

The "exclusive-or" type operation is also called symmetric set difference in set theory. Using this phrase in search, I found a page describing a number of techniques to implement the Symmetric Difference in SQL. It describes a couple of queries and how to optimise them. Although the details appear to be specific to Oracle, the general techniques are probably applicable to any DBMS.

Answer 2

There's another way to do what you want, using a FULL OUTER JOIN with a WHERE clause to remove the rows that appear in both tables. This is probably more efficient than the constructs you suggested, but you should of course measure the performance of both to be sure. Here's a query you might be able to use:

SELECT COALESCE(A.id, B.id) AS id
FROM A
FULL OUTER JOIN B
ON A.id = B.id
WHERE A.id IS NULL OR B.id IS NULL