CODEWITHSUNDEEP
mysql
Lee
Lee

Reputation: 999

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

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?

Upvotes: 2

Views: 1554

Answers (4)

lkgifford
lkgifford

Reputation: 1

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;

Upvotes: 0

user166390
user166390

Reputation:

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.

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

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

Upvotes: 2

GolezTrol
GolezTrol

Reputation: 116110

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.

Upvotes: 1

Related Questions