Reputation: 25912
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
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
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
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:
For the first one where the resultset can be from 2 different tables, let's take two tables: science_student and math_student.
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
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