MySQL——有可能得到两个查询结果的“差异”吗?

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?

我需要将两个查询结果合并为union，但我只想保留两个结果之间的差异。这是可能的吗?

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中选择了不允许的资源，显然我需要最后一个结果中的允许的资源。

In pseodo-code:

在pseodo-code:

Query1 - Query2

Queryresult 1:

+-------+
|  id   |
+-------+
|   1   |
+-------+
|   2   |
+-------+
|   3   |
+-------+
|   4   |
+-------+
|   5   |
+-------+
|   6   |
+-------+

Queryresult 2:

+-------+
|  id   |
+-------+
|   2   |
+-------+
|   5   |
+-------+

Needed:

需要:

+-------+
|  id   |
+-------+
|   1   |
+-------+
|   3   |
+-------+
|   4   |
+-------+
|   6   |
+-------+

4 个解决方案

#1

Like this, using NOT IN:

像这样使用NOT IN:

SELECT id FROM queryOneTable
WHERE id NOT IN (
    SELECT id FROM queryTwoTable
)

#2

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

#3

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 one id 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(或者至少它曾经是)，以掌握外部连接(s.a. left)，因为它比编写嵌套的选择更具有性能。

#4

There will be EXCEPT command in MariaDB in version 10.3.

除了第10.3版的MariaDB命令外，还有其他命令。

Meanwhile, if you need full difference, and not only on one field, you can use CONCAT workaround. 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 IFNULL check for fields that might contain null values.

如果字段的值可以包含逗号，请选择另一个分隔符而不是逗号。此外，在可能包含空值的字段中添加IFNULL检查。

#1