SQL规范是否提供了一种更好的方法来对两个集合进行异或运算？ [英] Does the SQL spec provide for a better way to do a exclusive ORing of two sets?

问题描述

我有一个结果集 A ，它是10行1-10 {1,2,3,4,5,6,7， 8,9,10} 和 B 这是10行，由偶数1-20 {2,4， 6,8,10,12,14,16,18,20} 。我想找到一组但不是全部都包含的元素。行中没有其他列。

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.

我知道 UNION 将是 A + B 。我可以在 A 和 B 中找到 A INTERSECT B 。我可以找到 A 中所有不在B中的不是的行，并带有 A而不是B 。

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.

这使我想到一个问题：如何找到A或B中的所有行，但不能同时找到这两个行？在SQL规范中（A除外B）UNION（B除外A）的传递对等项？我想要一组 {1,3,5,7,9,12,14,16,18,20} 。我相信这也可以写成除B以外的A（相交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?

我想这个必须出现在SQL规范中：我知道这件事是巨大的。

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

推荐答案

还有另一种方法，可以使用FULL OUTER JOIN和WHERE子句来删除出现在其中的行两个表。这可能比您建议的结构更有效，但是您当然应该测量两者的性能以确保。您可以使用以下查询：

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

这篇关于SQL规范是否提供了一种更好的方法来对两个集合进行异或运算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！