MySQL - Is it possible to get 'the difference' of two query results?

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 N开发者_如何学PythonOT-ALLOWED resources in Query 2, I obviously need the ALLOWED resources in my last result.

In pseodo-code:

Query1 - Query2

Queryresult 1:

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

Queryresult 2:

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

Needed:

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

---

Like this, using NOT IN:

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

---

I tested this query in SQLExpress, since I don't have MySql. I'm assuming it works the same way.

select x.id
from x 
left join y on x.id = y.id
where y.id is null

---

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.

---

There will be EXCEPT command in MariaDB in version 10.3.

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

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.