2014-03-04

Mysql dataset difference

SELECT *
  FROM MyTableA
 WHERE imageURL NOT IN (SELECT imageURL FROM MyTableB)

SELECT a.id FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL
SELECT b.id FROM b LEFT JOIN a ON b.id = a.id WHERE a.id IS NULL 

You can also use a left outer join (the first tells you where a row exists in table a and not b, the second vice-versa):

select Table4_20140226.PubMed_ID from Table4_20140226 LEFT JOIN pubmed_information ON Table4_20140226.PubMed_ID = pubmed_information.PubMed_ID WHERE pubmed_information.PubMed_ID IS NULL; 

SELECT DISTINCT value FROM table_a
INNER JOIN table_b
USING (value);

+-------+
| value |
+-------+
| B     |
+-------+

SELECT DISTINCT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);

+-------+
| value |
+-------+
| B     |
+-------+