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

mysql sql intersection

Ropstah · Oct 15, 2009 · Viewed 34.2k times · Source

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.

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   |
+-------+

Answer

Like this, using NOT IN:

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