MySQL：两个结果集的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2723839/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: difference of two result sets
提问by Zombies
How can I get the set difference of two result sets?
如何获得两个结果集的集差？
Say I have a result set (just one column in each):
假设我有一个结果集（每列只有一列）：
result1:
'a'
'b'
'c'
result2:
'b'
'c'
I want to minus what is in result1 by result2: result1 - result2 such that it equals:
我想通过 result2 减去 result1 中的内容： result1 - result2 使其等于：
difference of result1 - result2:
'a'
回答by rjh
To perform result1 - result2, you can join result1 with result2, and only output items that exist in result1. For example:
执行result1-result2，可以joinresult1和result2，只输出result1中存在的项。例如：
SELECT DISTINCT result1.column
FROM result1 LEFT JOIN result2 ON result1.column = result2.column
WHERE result2.column IS NULL
Note that is not a set difference, and won't output items in result2 that don't exist in result1. It's set subtraction.
请注意，这不是 set difference，并且不会输出 result2 中不存在于 result1 中的项目。它是集合减法。
See also: Web archive'd version of relevant blog post.
另请参阅：相关博客文章的网络存档版本。
回答by Cloom Magoo
If you want things in result1that are not in result2, what about:
如果您想要result1不在 中的东西，那该result2怎么办：
SELECT distinct result1
FROM t1
WHERE result1 NOT IN (select distinct result2 from t2);
Or:
或者：
SELECT distinct result
from t1 t
where NOT EXISTS (select 1 from t2 where result2 = t.result1)
NOTE: if result1is a subset of result2then the above queries will return an empty set (they won't show you things in result2that are not in result1) so they are not set difference, but may be useful too (probably it's more efficient than the outer join).
注意：如果result1是 的一个子集，result2那么上面的查询将返回一个空集（它们不会显示result2不在 中的东西result1）所以它们没有设置差异，但也可能有用（可能它比外部更有效）加入）。
