MySQL - 是否有可能获得两个查询结果的“差异”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1569990/
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 - Is it possible to get 'the difference' of two query results?
提问by Ropstah
I need to merge two query results as in union, but I want to only keep the difference between the two results. Is this possible?
我需要像联合一样合并两个查询结果，但我只想保留两个结果之间的差异。这可能吗？
I am basically selecting ALL resources in Query 1, and NOT-ALLOWED resources in Query 2, I obviously need the ALLOWED resources in my last result.
我基本上是在查询 1 中选择所有资源，在查询 2 中选择 NOT-ALLOWED 资源，我显然需要在上一个结果中使用 ALLOWED 资源。
In pseodo-code:
在伪代码中：
Query1 - Query2
Queryresult 1:
查询结果1：
+-------+
| id |
+-------+
| 1 |
+-------+
| 2 |
+-------+
| 3 |
+-------+
| 4 |
+-------+
| 5 |
+-------+
| 6 |
+-------+
Queryresult 2:
查询结果2：
+-------+
| id |
+-------+
| 2 |
+-------+
| 5 |
+-------+
Needed:
需要：
+-------+
| id |
+-------+
| 1 |
+-------+
| 3 |
+-------+
| 4 |
+-------+
| 6 |
+-------+
回答by nickf
Like this, using NOT IN:
像这样，使用NOT IN：
SELECT id FROM queryOneTable
WHERE id NOT IN (
SELECT id FROM queryTwoTable
)
回答by John Fisher
I tested this query in SQLExpress, since I don't have MySql. I'm assuming it works the same way.
我在 SQLExpress 中测试了这个查询，因为我没有 MySql。我假设它的工作方式相同。
select x.id
from x
left join y on x.id = y.id
where y.id is null
回答by Sven
The left join approach is more versatile since you can use it on two tables (or any two query result sets) where the uniqueness does not consist of oneid but of a combo of several column values. Also it is considered better SQL (or at least it used to be) to master outer joins (s.a. left) since it is more performant than writing nested selects.
左连接方法更通用，因为您可以在两个表（或任何两个查询结果集）上使用它，其中唯一性不是由一个id组成，而是由多个列值的组合组成。此外，它被认为是更好的 SQL（或至少曾经是）来掌握外连接（左），因为它比编写嵌套选择更高效。
回答by izogfif
There will be EXCEPTcommand in MariaDBin version 10.3.
10.3 版本中会有EXCEPT命令MariaDB。
Meanwhile, if you need full difference, and not only on one field, you can use CONCATworkaround. The idea is to concatenate all fields of your first query and add
同时，如果您需要完全不同，而不仅仅是在一个领域，您可以使用CONCAT解决方法。这个想法是连接你的第一个查询的所有字段并添加
HAVING CONCAT_WS(',', field_names) NOT IN (
SELECT CONCAT(',', fields) FROM other_query
)
Pick up another delimiter instead of comma if fields' values can contain comma. Also, add IFNULLcheck for fields that might contain null values.
如果字段的值可以包含逗号，则选择另一个分隔符而不是逗号。此外，添加IFNULL对可能包含空值的字段的检查。
