MySQL: difference of two result sets

Question

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' 

Answer 1

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 

Note that is not a set difference, and won't output items in result2 that don't exist in result1. It's set subtraction.

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

Answer 2

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).