21

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   |
+-------+
PeeHaa
  • 71,436
  • 58
  • 190
  • 262
Ropstah
  • 17,538
  • 24
  • 120
  • 194
  • The requirement is unclear. The text explanation mentions taking ALL of query1 but then the example contradict this. Let's try to define the requirement solely through the example. If query2 had 7 or 0 would these values be included in the difference ? – mjv Oct 15 '09 at 02:42
  • 1
    I'll agree that the title doesn't align with the question body, but the question itself is quite clear. The OP asks for subtraction: all resources which are not "not available": `A - B` – nickf Oct 15 '09 at 03:59
  • For a minute, I thought he was asking to subtract query 1 field values to query 2 field values. – putolaruan Oct 15 '09 at 07:33
  • Sorry for being unclear, but ALL RESOURCES means all records from the table RESOURCE are included in QUERY1. So 7 will never be in QUERY2 if it isn't in QUERY1 – Ropstah Oct 15 '09 at 13:35

4 Answers4

57

Like this, using NOT IN:

SELECT id FROM queryOneTable
WHERE id NOT IN (
    SELECT id FROM queryTwoTable
)
nickf
  • 537,072
  • 198
  • 649
  • 721
  • In MySQL 8.0 you can use `WITH` common table expressions to define queryOneTable and queryTwoTable in case each of those is a complex subquery. – ColinM Oct 07 '20 at 20:51
4

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
John Fisher
  • 22,355
  • 2
  • 39
  • 64
2

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.

izogfif
  • 6,000
  • 2
  • 35
  • 25
1

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.

Ravi
  • 30,829
  • 42
  • 119
  • 173
Sven
  • 11
  • 1