CODEWITHSUNDEEP
mysqlset-difference
Zombies
Zombies

Reputation: 25912

MySQL: difference of two result sets

How can I get the set difference of two result sets?

Say I have a result set (just one column in each):

result1:
'a'
'b'
'c'

result2:
'b'
'c'

I want to minus what is in result1 by result2: result1 - result2 such that it equals:

 difference of result1 - result2:
 'a'

Upvotes: 48

Views: 82438

Answers (4)

sify
sify

Reputation: 741

There is actually an official chapter on "Set Operations with UNION, INTERSECT, and EXCEPT", I can't believe no one mentions that. Just do:

select * from result1 except select * from result2

Upvotes: 1

rjh
rjh

Reputation: 50324

To perform result1 - result2, you can join result1 with result2, and only output items that exist in result1. For example:

SELECT DISTINCT result1.column
FROM result1 LEFT JOIN result2 ON result1.column = result2.column
WHERE result2.column IS NULL

In set theory terms this is a set difference of result1 and result2, i.e. elements in result1 but not in result2.

If you are looking for a symmetric difference (elements in either result1 or result2, but not both), you can do a FULL OUTER JOIN and filter out elements where either side is NULL.

Finally, as a caveat, this won't work well if you have nullable columns in your database, as these will be seen identically to a row where the join failed. Thankfully, joins are normally performed on indexed columns, where nullable values are rare.

See also: Web archive'd version of relevant blog post.

Upvotes: 68

Rito
Rito

Reputation: 3298

I recently had this requirement where I had to find the difference between two resultsets. Although the above answers helped me wished they were a little detailed. For the given question I found two interpretation:

  1. The resultset could be from 2 different tables
  2. The resultset from the same table

For the first one where the resultset can be from 2 different tables, let's take two tables: science_student and math_student.


science_student
enter image description here


math_student
enter image description here


I want to calculate the difference between these 2 resultsets, that is: 
result1 - result2

result1: select student_id from science_student where id > 2

result2: select student_id from math_student

The difference between result1 - result2 is STUD3

So the query to find the difference will be:

select result1.student_id 
 from 
 (select student_id from science_student where id > 2) result1
 left join
 (select student_id from math_student) result2
 on result1.student_id = result2.student_id 
 where result2.student_id is null;



For the second interpretation where the resultset can be from the same table:

result1 - result2

result1: select student_id from science_student 

result2: select student_id from science_student where id > 2

The difference between result1 - result2 is STUD1, STUD2

And the query for the same will be:

select result1.student_id 
 from 
 (select student_id from science_student) result1
 left join
 (select student_id from science_student where id > 2) result2
 on result1.student_id = result2.student_id 
 where result2.student_id is null;

Upvotes: 1

Cloom Magoo
Cloom Magoo

Reputation: 496

If you want things in result1 that are not in result2, what about:

SELECT distinct result1
FROM t1 
WHERE result1 NOT IN (select distinct result2 from t2);

Or: 

SELECT distinct result
from t1 t
where NOT EXISTS (select 1 from t2 where result2 = t.result1)

NOTE: if result1 is a subset of result2 then the above queries will return an empty set (they won't show you things in result2 that are not in result1) so they are not set difference, but may be useful too (probably it's more efficient than the outer join).

Upvotes: 18

Related Questions