Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 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   |
+-------+
like image 606
Ropstah Avatar asked Oct 15 '09 02:10

Ropstah


People also ask

How can I find the difference between two queries in MySQL?

The MINUS operator is one of three set operators in the SQL standard that includes UNION , INTERSECT , and MINUS . The MINUS compares the results of two queries and returns distinct rows from the result set of the first query that does not appear in the result set of the second query.

How do you get the difference between two query results in SQL?

The Minus Operator in SQL is used with two SELECT statements. The MINUS operator is used to subtract the result set obtained by first SELECT query from the result set obtained by second SELECT query.

How do you compare two queries performance?

We can compare the execution plan of the already saved query execution plan as well. To do so, just open the query execution plan in SQL Server Management Studio 2016. Once opened, right click on the execution plan, and click on the Showplan compare.

What is the difference between two result?

Answer: To find the difference between two numbers, subtract the number with the smallest value from the number with the largest value. The product of this sum is the difference between the two numbers.


1 Answers

Like this, using NOT IN:

SELECT id FROM queryOneTable
WHERE id NOT IN (
    SELECT id FROM queryTwoTable
)
like image 155
nickf Avatar answered Oct 17 '22 06:10

nickf