MySQL: difference of two result sets

mysql set-difference
Zombies picture Zombies · Apr 27, 2010 · Viewed 67k times · Source

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

rjh picture rjh · Apr 27, 2010

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.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More