Select from table1 where similar rows do NOT appear in table2?

Question

I've been struggling with this for a while, and haven't been able to find any examples to point me in the right direction.

I have 2 MySQL tables that are virtually identical in structure. I'm trying to perform a query that returns results from Table 1 where the same data isn't present in table 2. For example, imagine both tables have 3 fields - fieldA, fieldB and fieldC. I need to exclude results where the data is identical in all 3 fields.

Is it even possible?

possible duplicate of [Select * from table1 that does not exist in table2 with conditional](http://stackoverflow.com/questions/1598322/select-from-table1-that-does-not-exist-in-table2-with-conditional) -- the selected answer has a very good article at the bottom which discusses performance. — , Sep 23 '11 at 21:30

score 2 · Answer 1 · answered Sep 23 '11 at 21:03

2

There are several ways to do it (assuming the fields don't allow NULLs):

SELECT a, b, c FROM Table1 T1 WHERE NOT EXISTS 
  (SELECT * FROM Table2 T2 WHERE T2.a = T1.a AND T2.b = T1.b AND T2.c = T1.c)

or

SELECT T1.a, T1.b, T1.c FROM Table1 T1 
   LEFT OUTER JOIN Table2 T2 ON T2.a = T1.a AND T2.b = T1.b AND T2.c = T1.c
   WHERE T2.a IS NULL

answered Sep 23 '11 at 21:03

Larry Lustig

49,320
14
110
160

I found this interesting article: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ -- it argues against NOT EXISTS. Fickle databases. – Sep 23 '11 at 21:38
A relatively insignificant 0.2 seconds difference in their test but yes, MySQL is known to historically have poor performance on (particularly) IN () queries that other databases can execute much more quickly. – Larry Lustig Sep 23 '11 at 21:45

score 1 · Answer 2 · answered Sep 23 '11 at 21:01

select
  t1.*
from
  table1 t1
  left join table2 t2 on 
    t1.fieldA = t2.fieldA and
    t1.fieldB = t2.fieldB and
    t1.fieldC = t2.fieldC
where 
  t2.fieldA is null

Note that this will not work if any of the fields is NULL in both tables. The expression NULL = NULL returns false, so these records are excluded as well.

score 0 · Answer 3 · answered Jul 18 '13 at 20:23

The 'left join' is very slow in MYSQL. The gifford algorithm shown below speeds it many orders of magnitude.

select * from t1
inner join
(select fieldA from
  (select distinct fieldA, 1 as flag from t1
     union all
   select distinct fieldA, 2 as flag from t2) a
 group by fieldA
 having sum(flag) = 1) b on b.fieldA = t1.fieldA;

score 0 · Accepted Answer · 2011-09-23T21:34:18.907

This is a perfect use of EXCEPT (the key word/phase is "set difference"). However, MySQL lacks it. But no fear, a work-around is here:

Intersection and Set-Difference in MySQL (A workaround for EXCEPT)

Please not that approaches using NOT EXISTS in MySQL (as per above link) are actually less than ideal although they are semantically correct. For an explanation of the performance differences with the above (and alternative) approaches as handled my MySQL, complete with examples, see NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL:

That’s why the best way to search for missing values in MySQL is using a LEFT JOIN / IS NULL or NOT IN rather than NOT EXISTS.

Happy coding.

First time I've come across EXCEPT before, but I'll look into the workaround - thanks! — Lee, Sep 23 '11 at 21:04

Select from table1 where similar rows do NOT appear in table2?

4 Answers4