Does the SQL specification offer the best way to make an 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 elements that are in the same set, but not both. There are no other columns in the rows.

I know UNION will be A + B I can find them in A and B using A INTERSECT B I can find all rows in A that are not in B with A EXCEPT B

This leads me to the question of how to find all the rows that are in A or B, but not both, is there a transitive equiv ( A EXCEPT B ) UNION ( B EXCEPT A) in the sql specification? I want 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 mathematical reason in set theory why this cannot be done in one operation (which can be explained to those who do not understand set theory)? Or is it simply not realized because it is so simple to build yourself? Or I just don’t know how best to do this?

I think it should be in the SQL spec somewhere: I know that thing is great.

Answer 1

Another way to do what you want is to use the FULL OUTER JOIN with the WHERE clause to remove the rows that appear in both tables. This is probably more efficient than the designs you proposed, but you must, of course, measure the performance of both to be sure. Here is a query you could 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 

Answer 2

The operation "exclusive or type" is also called the symmetric difference of sets in set theory. Using this phrase in a search, I found a page that describes a number of methods for implementing Symmetric Difference in SQL . It describes a couple of queries and how to optimize them. Although the details seem to be specific to Oracle, the general methods are likely to apply to any DBMS.